如何编写SQL查询生成含item与sub数组的指定嵌套JSON结构？

我来帮你搞定这个问题！你想要的是将一对多关联的两张表数据，转换成外层为数组、每个元素包含item嵌套对象的JSON格式，之前的查询没能正确构建层级结构，下面是可行的解决方法：

问题分析 #

你的原查询用了FOR JSON PATH, root('item')，但这个写法会把所有结果打包进一个item根对象里，而不是每个主表记录对应一个独立的item对象并组成数组，同时也没正确生成sub嵌套数组。

正确的SQL查询 #

DECLARE @T1 TABLE(RootId int, [Name] varchar(50))
DECLARE @T2 TABLE(Id int,RootId int, Category varchar(50))
INSERT INTO @T1 VALUES(1,'Some Name 12121'),(2,'Some Name 343434')
INSERT INTO @T2 VALUES(100,1,'Category 3333'), (101,1,'Category 2222'), (102,1,'Category 4444'), (103,1,'Category 5555'), (104,2,'Category 1111'), (105,2,'Category 77777')

SELECT
  (
    SELECT
      T1.RootId AS rootId,
      T1.Name AS name,
      (
        -- 子查询生成对应主记录的sub数组
        SELECT Id, Category
        FROM @T2 T2
        WHERE T2.RootId = T1.RootId
        FOR JSON PATH
      ) AS sub
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER -- 去掉内层数组包装，转为单个对象
  ) AS item
FROM @T1 T1
FOR JSON PATH; -- 外层生成包含所有item对象的数组

结果说明 #

执行后会生成完全符合你需求的JSON：

[
  {
    "item": {
      "rootId": 1,
      "name": "Some Name 12121",
      "sub": [
        {"id":100,"category":"Category 3333"},
        {"id":101,"category":"Category 2222"},
        {"id":102,"category":"Category 4444"},
        {"id":103,"category":"Category 5555"}
      ]
    }
  },
  {
    "item": {
      "rootId": 2,
      "name": "Some Name 343434",
      "sub": [
        {"id":104,"category":"Category 1111"},
        {"id":105,"category":"Category 77777"}
      ]
    }
  }
]

如果只需要生成RootId=1的记录，只需在主查询末尾添加WHERE T1.RootId = 1即可。

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