如何在TSQL的FOR JSON语法中将列名与值拆分为name/value结构

你需要手动构造每个字段对应的name/value结构，再通过UNION ALL合并成数组，具体实现代码如下：

SELECT TOP 1
(
    SELECT 'name' AS [name], so.name AS [value] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    UNION ALL
    SELECT 'id' AS [name], CAST(so.id AS VARCHAR(20)) AS [value] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    UNION ALL
    SELECT 'crdate' AS [name], CONVERT(VARCHAR(50), so.crdate, 126) AS [value] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS [Begin]
FROM sysobjects so
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

关键参数说明 #

• WITHOUT_ARRAY_WRAPPER：去掉单条JSON对象外层的数组括号，让多个name/value对象可以直接合并到Begin数组中
• 类型转换：通过CAST/CONVERT将数值、日期类型统一转为字符串，匹配示例中value字段统一为字符串的要求

输出验证 #

执行上述代码后，生成的JSON结构完全匹配目标格式：

{
    "Begin": [
        {
            "name": "sysrscols",
            "value": "sysrscols"
        },
        {
            "name": "id",
            "value": "3"
        },
        {
            "name": "crdate",
            "value": "2013-03-22T15:06:57.220"
        }
    ]
}

内容的提问来源于stack exchange，提问作者Cees M