如何在SQL Server查询中生成指定结构的JSON集合

目标JSON结构 #

{
  "data":[
    {"id":3,"type":1,"job":1},
    {"id":4,"type":2,"job":34}
  ],
  "collections": {
    "jobs":[
      {"key":1,"label":"Fenchurch"},
      {"key":34,"label":"Raggle"}
    ],
    "users":[
      {"id":5,"label":"Bob"},
      {"id":20,"label":"Jeff"}
    ]
  }
}

现有尝试的问题 #

• 第一次尝试：collections被错误包裹为数组（"collections":[{"..."}]），原因是内层生成jobs和users的子查询使用FOR JSON PATH后默认返回数组，作为外层字段时被嵌套成数组结构。
• 第二次尝试：使用CONCAT_WS()拼接JSON字符串导致转义错误，且jobs和users结构混乱，因为拼接后的字符串会被外层FOR JSON自动转义，破坏原有JSON结构。

正确解决方案 #

核心思路是让collections对应的子查询生成单个JSON对象而非数组，通过WITHOUT_ARRAY_WRAPPER参数去掉内层的数组包裹，同时正确嵌套各层级查询：

SELECT 
(
    SELECT
        data = (
            SELECT TOP 2 * 
            FROM Event 
            FOR JSON PATH
        ),
        collections = (
            SELECT 
                jobs = (
                    SELECT TOP 2 id AS 'key', job_number AS 'label' 
                    FROM [Job] 
                    ORDER BY job_number DESC 
                    FOR JSON PATH
                ),
                users = (
                    SELECT TOP 2 id, full_name AS 'label' 
                    FROM [User] 
                    ORDER BY full_name 
                    FOR JSON PATH
                )
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS 'result'

代码说明 #

• data字段：直接从Event表取TOP 2数据，用FOR JSON PATH生成数组，匹配目标结构要求。
• collections字段：内层查询将jobs和users的JSON数组作为键值对，通过FOR JSON PATH, WITHOUT_ARRAY_WRAPPER生成单个对象，避免被包裹成数组。
• 最外层的FOR JSON PATH, WITHOUT_ARRAY_WRAPPER确保最终结果是单个JSON对象而非数组。

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