如何为SQL生成的JSON添加三层嵌套根节点（Sang>Udsling>Stur）

你当前使用的SQL语句通过FOR JSON PATH, ROOT('Stur')生成了仅包含Stur根节点的JSON，但需要将Stur嵌套在Udsling节点内，再将Udsling嵌套在Sang节点内，实现三层根结构。

当前SQL语句 #

SELECT id,
       (SELECT t2.att1 AS [ Anul ],
               t3.att2 AS [ Strur.Nuer ],
               t3.att3 AS [ Strur.Tyvn ] etc ..
          FROM table1 AS t2
          LEFT JOIN table2 AS t3
         WHERE t1.number = t2.number
           FOR JSON PATH, ROOT('Stur'))
  FROM table1 AS t1

解决方案：嵌套构造JSON层级 #

通过多层嵌套SELECT结合FOR JSON PATH和WITHOUT_ARRAY_WRAPPER参数，可简便实现三层嵌套结构，修改后的SQL如下：

SELECT 
  (
    SELECT 
      (
        -- 原内层查询，生成Stur根节点的JSON
        SELECT t2.att1 AS [Anul],
               t3.att2 AS [Strur.Nuer],
               t3.att3 AS [Strur.Tyvn] -- 补充你的其他字段
        FROM table1 AS t2
        LEFT JOIN table2 AS t3 ON t2.关联字段 = t3.关联字段 -- 需补全JOIN的关联条件，原SQL缺失
        WHERE t1.number = t2.number
        FOR JSON PATH, ROOT('Stur')
      ) AS Udsling
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER -- 取消外层数组，生成单个对象
  ) AS Sang
FROM table1 AS t1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER -- 最终取消外层数组，得到三层嵌套对象

逻辑说明 #

1. 最内层查询：保留原有业务逻辑，生成包含Stur根节点的JSON数组；
2. 中间层查询：将内层结果作为Udsling字段的值，通过WITHOUT_ARRAY_WRAPPER避免生成数组，直接转为单个对象；
3. 最外层查询：将中间层结果作为Sang字段的值，同样使用WITHOUT_ARRAY_WRAPPER，最终得到目标三层嵌套JSON结构。

注意事项 #

• 原SQL中的LEFT JOIN缺少关联条件（ON子句），实际运行时必须补充，否则会触发语法错误；
• 如果table1有多条数据，可根据需求配合TOP 1或特定筛选条件，确保生成单个三层嵌套对象而非数组。

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