如何将多组列数不同的SQL查询结果合并为单个JSON对象

需求说明 #

需动态构建SQL SELECT语句（最多生成50条，每条对应唯一邮箱），这些查询结果的列结构各不相同，最终要将所有结果合并成单个JSON数组，数组内每个对象对应一条邮箱的完整数据，保留各自的列字段。

示例查询结果 #

结果集1

结果集2

目标JSON格式 #

[
   {
      "Email":"abc@googlemail.com",
      "Age":"96",
      "Gender":"Male"
   },
   {
      "Email":"def@googlemail.com",
      "Age":"36",
      "AddressLine1":"1 Laneview Way",
      "Country":"England"
   }
]

当前尝试的SQL代码 #

SELECT User_Alias = JSON_QUERY(query1.json_result), User_Alias2 = JSON_QUERY(query2.json_result) FROM
(
SELECT Email,
       Age,
       Gender
FROM tableAbc
WHERE Email = 'abc@googlemail.com'
FOR JSON PATH
) AS query1 (json_result)
,(
SELECT Email,
       Age,
       AddressLine1,
       Country
FROM tableAbc
WHERE Email = 'def@googlemail.com'
FOR JSON PATH
) AS query2 (json_result)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

解决方案 #

当前写法会生成带User_Alias、User_Alias2键的嵌套结构，不符合目标格式。可通过先将单个查询转为JSON对象，再合并成数组的方式实现：

方法1：固定数量查询场景 #

直接用UNION ALL合并每个查询的JSON结果，再生成最终数组：

SELECT JSON_QUERY(json_data) AS json_obj
FROM (
    -- 第一个查询的JSON对象
    SELECT (
        SELECT Email, Age, Gender
        FROM tableAbc
        WHERE Email = 'abc@googlemail.com'
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS json_data
    UNION ALL
    -- 第二个查询的JSON对象
    SELECT (
        SELECT Email, Age, AddressLine1, Country
        FROM tableAbc
        WHERE Email = 'def@googlemail.com'
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS json_data
) AS temp
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

方法2：动态生成查询场景（适配最多50条动态需求） #

如果需要动态处理多个邮箱，可通过动态SQL拼接查询块后执行：

DECLARE @emails NVARCHAR(MAX) = N'''abc@googlemail.com'', ''def@googlemail.com''' -- 替换为动态获取的邮箱列表
DECLARE @dynamicSql NVARCHAR(MAX) = N''

SET @dynamicSql = N'
SELECT JSON_QUERY(json_data) AS json_obj
FROM (
    ' + STUFF((
        SELECT N'UNION ALL SELECT (
            SELECT * FROM tableAbc WHERE Email = ''' + TRIM(value) + N''' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        ) AS json_data'
        FROM STRING_SPLIT(@emails, ',')
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 10, '') + N'
) AS temp
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER'

EXEC sp_executesql @dynamicSql

核心说明 #

• 单个查询使用FOR JSON PATH, WITHOUT_ARRAY_WRAPPER生成单个JSON对象，而非数组；
• 通过UNION ALL将所有单个JSON对象的字符串合并到临时表；
• 外层用FOR JSON PATH, WITHOUT_ARRAY_WRAPPER将这些对象包裹成统一数组。

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