SQL Server中基于ID将JSON结果分组为嵌套数组的实现方法

要实现你需要的将相同Id的条目归入同一子数组的嵌套JSON结构，我们可以调整原来的FOR JSON AUTO语句，通过分组聚合+JSON数组构造的方式来完成，具体方案如下：

最终可用的SQL语句（替换存储过程中原有的JSON生成语句） #

SELECT 
    JSON_QUERY((
        SELECT accountid, Id, name 
        FROM TestJsonFormat t2 
        WHERE t2.Id = t1.Id 
        FOR JSON PATH
    )) AS matchItem
FROM TestJsonFormat t1
GROUP BY t1.Id
FOR JSON PATH, ROOT('match')

执行效果 #

这条语句会输出你期望的嵌套格式：

{
    "match": [
        [
            { "accountid":"12312312", "Id":1, "name":"Ace Signs Ltd" },
            { "accountid":"12312312", "Id":1, "name":"Ace Signs Ltd" }
        ],
        [
            { "accountid":"213123123", "Id":2, "name":"Workshare Technology" },
            { "accountid":"123123123", "Id":2, "name":"Workshare" }
        ]
    ]
}

语句逻辑拆解 #

• 内层子查询：针对每个唯一的Id，查询该Id对应的所有记录，用FOR JSON PATH生成这个Id专属的JSON数组。
• JSON_QUERY函数：这里必须用它来处理内层的JSON数组，否则SQL Server会自动给数组添加引号和转义字符，导致嵌套结构出错。它的作用是告诉SQL Server：这部分是合法的JSON，直接嵌入结果即可。
• GROUP BY t1.Id：按Id分组，确保每个唯一Id只生成一条外层记录，避免重复的分组结果。
• 外层的FOR JSON PATH, ROOT('match')：把所有分组后的子数组包裹在match根节点下，形成最终的外层结构。

测试数据补充说明 #

如果你还没创建测试表，先执行以下SQL初始化数据（注意accountid是varchar类型，值要加单引号）：

CREATE TABLE TestJsonFormat ( 
    accountid varchar(255), 
    Id int, 
    name varchar(255) 
)
insert into TestJsonFormat values('12312312',1,'Ace Signs Ltd')
insert into TestJsonFormat values('213123123',2,'Workshare Technology')
insert into TestJsonFormat values('12312312',1,'Ace Signs Ltd')
insert into TestJsonFormat values('123123123',2,'Workshare')

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