如何为自引用表生成含Children字段的嵌套JSON数组

要生成带subChild（可自定义为Children）的嵌套JSON结构，你需要修改递归CTE的写法，让每个父节点递归包含其子节点的JSON数组。以下是适配你需求的SQL代码：

WITH cte_assets AS (
    -- 锚点成员：获取所有根节点（ParentId为NULL）
    SELECT 
        id,
        Part AS name,
        CAST(NULL AS NVARCHAR(MAX)) AS subChild
    FROM dbo.Assets
    WHERE ParentId IS NULL

    UNION ALL

    -- 递归成员：获取子节点，并关联父节点生成嵌套JSON
    SELECT 
        e.id,
        e.Part AS name,
        -- 子查询生成当前节点的直接子节点JSON数组
        (
            SELECT id, Part AS name
            FROM dbo.Assets
            WHERE ParentId = e.id
            FOR JSON PATH
        ) AS subChild
    FROM dbo.Assets e
    INNER JOIN cte_assets o ON o.id = e.ParentId
)
-- 仅查询根节点，生成完整嵌套JSON
SELECT id, name, subChild
FROM cte_assets
WHERE ParentId IS NULL
FOR JSON PATH, ROOT('assets');

代码说明 #

• 锚点成员：先筛选出所有顶级节点（ParentId IS NULL），初始化subChild为NULL，后续递归填充子节点数据。
• 递归成员：对每个节点，通过子查询生成其直接子节点的JSON数组，赋值给subChild字段，用FOR JSON PATH保证子节点的JSON格式正确。
• 最终查询：只保留根节点（子节点已嵌套在父节点的subChild中），再通过FOR JSON PATH生成完整的层级结构，ROOT('assets')可指定根节点名称，不需要的话直接去掉即可。

针对你的数据，输出示例（简化版） #

{
  "assets": [
    {
      "id": 1,
      "name": "HeaterAsset",
      "subChild": [
        {"id": 3,"name": "Body"},
        {
          "id": 5,
          "name": "GearBox",
          "subChild": [
            {
              "id": 2,
              "name": "Motor",
              "subChild": [{"id":4,"name":"Coil"},{"id":6,"name":"Shaft"}]
            },
            {"id":7,"name":"Gears"}
          ]
        }
      ]
    },
    {
      "id": 8,
      "name": "FanAsset",
      "subChild": [
        {
          "id":9,
          "name":"Body",
          "subChild": [{"id":10,"name":"Fance"}]
        },
        {
          "id":11,
          "name":"Motor",
          "subChild": [{"id":12,"name":"Coil"},{"id":13,"name":"Shaft"}]
        }
      ]
    }
  ]
}

原代码无法生成嵌套JSON的原因 #

你之前的CTE只是把所有节点平铺查询出来，FOR JSON AUTO只能根据查询列的简单关系生成扁平JSON，无法识别树形结构的层级嵌套逻辑，必须通过递归构造每个节点的子节点JSON数组才能实现需求。

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