SQL Server生成带children嵌套结构的web树形JSON数据

需求说明 #

在SQL Server中存储的树形结构表数据，需要转换为适配Web树组件的JSON格式：

• 通过children字段维护层级关系
• 所有后代节点以嵌套JSON对象形式挂载到对应父节点下

测试表结构与预置数据 #

DROP TABLE IF EXISTS #tTree;

CREATE TABLE #tTree
(
     id INTEGER IDENTITY(1,1),
     text VARCHAR(256),
     parentId INTEGER,
     path VARCHAR(256),
     depth TINYINT,
     leaf TINYINT,
     expanded TINYINT
);

INSERT INTO #tTree (text, parentId, path, depth, leaf, expanded)
VALUES ('Category 1', null, '1', 1, null, 1),
       ('SubCategory 1', 1, '1,2', 2, null, 1),
       ('Element 1', 2, '1,2,3', 3, 1, null),
       ('Category 2', null, '4', 1, null, 1),
       ('SubCategory 2', 4, '4,5', 2, 1, null),
       ('SubCategory 3', 4, '4,6', 2, 1, null),
       ('Element 2', 4, '4,7', 2, null, 1),
       ('SubElement 1', 5, '4,5,8', 3, 1, null),
       ('SubSubCategory 1', 2, '1,2,9', 3, 1, null),
       ('Category 3', null, '10', 1, 1, null)

示例数据查询结果：

目标输出格式 #

需要生成的带嵌套children结构的JSON如下：

[
   {
     "id":1,
     "text":"Category 1",
     "path":"1",
     "depth":1, 
     "expanded":1,
     "children":[{
        "id":2,
        "text":"SubCategory 1",
        "parentId":1,
        "path":"1,2",
        "depth":2,
        "expanded":1,
        "children":[
            {"id":3,"text":"Element 1","parentId":2,"path":"1,2,3","depth":3,"leaf":1},
            {"id":9,"text":"SubSubCategory 1","parentId":2,"path":"1,2,9","depth":3,"leaf":1}
        ]
     }]
    },
    {"id":10,"text":"Category 3","path":"10","depth":1,"leaf":1},
    {"id":4,
     "text":"Category 2",
     "path":"4",
     "depth":1,
     "expanded":1,
     "children":[
        {"id":5,
         "text":"SubCategory 2",
         "parentId":4,
         "path":"4,5",
         "depth":2,
         "expanded":1,
         "children":[
            {"id":8,"text":"SubElement 1","parentId":5,"path":"4,5,8","depth":3,"leaf":1}
         ]
        },
        {"id":6,"text":"SubCategory 3","parentId":4,"path":"4,6","depth":2,"leaf":1},
        {"id":7,"text":"Element 2","parentId":4,"path":"4,7","depth":2,"leaf":1}    
     ]
    }
]

现有问题 #

原有递归CTE写法仅输出平铺的行集，FOR JSON AUTO无法自动识别层级关系生成嵌套children结构，代码如下：

;WITH cteTree AS
(
    SELECT
         tree.id
        ,tree.text
        ,tree.parentId
        ,tree.path
        ,tree.depth
        ,tree.leaf
        ,tree.expanded
    FROM 
        #tTree AS tree
    WHERE 
        parentId IS NULL

    UNION ALL

    SELECT
         tree.id
        ,tree.text
        ,tree.parentId
        ,tree.path
        ,tree.depth
        ,tree.leaf
        ,tree.expanded
    FROM 
        #tTree AS tree
    INNER JOIN 
        cteTree ON tree.parentId = cteTree.id
)
SELECT * 
FROM cteTree
ORDER BY path ASC
FOR JSON AUTO

FOR JSON AUTO只能根据查询中多表关联的外键关系生成嵌套结构，平铺的递归CTE结果集无法直接生成目标格式，需要从最深层节点向上递归，为每个节点拼装子节点的JSON数组。

可直接使用的SQL代码如下：

;WITH cteMaxDepth AS (
    -- 计算树的最大深度，作为递归锚点
    SELECT MAX(depth) AS maxDepth FROM #tTree
),
cteBuildTree AS (
    -- 递归锚点：取最深层节点，这类节点没有子节点，children初始化为空数组
    SELECT 
        id, text, parentId, path, depth, leaf, expanded,
        childrenJson = CAST('[]' AS NVARCHAR(MAX))
    FROM #tTree, cteMaxDepth md
    WHERE depth = md.maxDepth

    UNION ALL

    -- 递归向上层节点遍历，聚合当前节点的所有直接子节点生成children数组
    SELECT 
        parentNode.id, 
        parentNode.text, 
        parentNode.parentId, 
        parentNode.path, 
        parentNode.depth, 
        parentNode.leaf, 
        parentNode.expanded,
        childrenJson = (
            SELECT 
                childNode.id, 
                childNode.text, 
                childNode.parentId, 
                childNode.path, 
                childNode.depth, 
                childNode.leaf, 
                childNode.expanded,
                JSON_QUERY(childNode.childrenJson) AS children
            FROM cteBuildTree childNode
            WHERE childNode.parentId = parentNode.id
            FOR JSON PATH
        )
    FROM #tTree parentNode
    WHERE EXISTS (SELECT 1 FROM cteBuildTree c WHERE c.parentId = parentNode.id)
)
-- 最终仅筛选根节点（parentId为NULL）输出，自动携带所有嵌套子节点
SELECT 
    id, 
    text, 
    path, 
    depth, 
    leaf, 
    expanded,
    JSON_QUERY(childrenJson) AS children
FROM cteBuildTree
WHERE parentId IS NULL
FOR JSON PATH

关键说明 #

• 递归方向从最深层叶子节点向上构建，避免全表重复扫描，性能更优
• 必须使用JSON_QUERY()包裹子节点生成的JSON字符串，否则FOR JSON会将子节点JSON转义为普通字符串
• SQL Server默认会自动忽略值为NULL的字段，输出结果和目标格式完全匹配
• 该写法支持任意深度的树形结构，不需要提前写死层级

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