如何用SQL将查询结果的label列值作为JSON输出的键？

现有可正常运行的SQL查询：

SELECT(
    SELECT 
        a.id,
        a.label,
        a.text
    FROM [data] a 
    FOR JSON PATH, INCLUDE_NULL_VALUES
) AS 'data'

该查询生成的JSON结构：

[
    {
        "id": "N/A",
        "label": "test",
        "text": "Not applicable"
    },
    {
        "id": "ID",
        "label": "test2",
        "text": "Identification"
    }
]

期望得到的JSON结构（将label列的值作为键）：

[{
    "test": {
        "id": "N/A",
        "text": "Not applicable"
    },
    "test2": {
        "id": "ID",
        "text": "Identification"
    }
}]

请问能否不通过字符串拼接，直接用SQL实现该需求？

可以直接用SQL实现，无需字符串拼接。核心是利用JSON_OBJECT构造动态键值对，结合FOR JSON的参数控制结构：

方案1（适配多数支持JSON函数的SQL版本） #

SELECT 
    JSON_QUERY(
        (
            SELECT 
                JSON_OBJECT(
                    a.label, JSON_OBJECT('id', a.id, 'text', a.text)
                )
            FROM [data] a
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES
        )
    ) AS 'data'

方案2（SQL Server 2022+版本更简洁写法） #

SELECT 
    (
        SELECT 
            STRING_AGG(
                JSON_QUERY(JSON_OBJECT(a.label, JSON_OBJECT('id', a.id, 'text', a.text))), ','
            )
        FROM [data] a
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS 'data'

关键逻辑说明 #

• JSON_OBJECT(a.label, ...)：将label字段的值作为动态键，对应值为包含id和text的子JSON对象
• WITHOUT_ARRAY_WRAPPER：取消默认的数组包裹，让多个键值对直接合并为单个对象
• JSON_QUERY：确保生成的内容被识别为JSON类型，而非字符串格式

内容的提问来源于stack exchange，提问作者Mr. Blonde