SQL Server用递归CTE生成N级嵌套JSON出现节点重复如何解决

根因分析 #

原递归CTE采用单条子节点匹配上卷的逻辑，同一个父节点下存在多少个子节点，就会生成多少条对应父节点的记录，每条记录仅携带一个子节点，不会自动合并同父节点的所有子节点到同一个children数组中，最终导致节点重复输出。

修正后代码 #

;WITH Hierarchy AS (
    -- 初始查询所有叶子节点，标记初始层级为0
    SELECT 
        propertyID, 
        parentID, 
        title,
        class,
        typeid,
        value,
        0 AS depth,
        CAST('[{}]' AS NVARCHAR(MAX)) AS children
    FROM 你的原始表名 t
    WHERE NOT EXISTS (SELECT 1 FROM 你的原始表名 t2 WHERE t2.parentID = t.propertyID)
    
    UNION ALL
    
    -- 向上递归计算所有节点的层级
    SELECT 
        t.propertyID, 
        t.parentID, 
        t.title,
        t.class,
        t.typeid,
        t.value,
        h.depth + 1 AS depth,
        CAST('[{}]' AS NVARCHAR(MAX)) AS children
    FROM 你的原始表名 t
    INNER JOIN Hierarchy h ON t.propertyID = h.parentID
),
MaxDepth AS (
    -- 获取节点最大层级，作为上卷起始点
    SELECT MAX(depth) AS max_d FROM Hierarchy
),
Rollup AS (
    -- 从最深层级节点开始，生成初始子节点结构
    SELECT 
        h.propertyID, 
        h.parentID, 
        h.title,
        h.class,
        h.typeid,
        h.value,
        h.depth,
        JSON_QUERY((
            SELECT 
                h2.propertyID, 
                h2.title,
                h2.class,
                h2.typeid,
                h2.value,
                JSON_QUERY(h2.children) AS children
            FROM Hierarchy h2
            WHERE h2.parentID = h.propertyID
            FOR JSON PATH
        )) AS children
    FROM Hierarchy h
    CROSS JOIN MaxDepth md
    WHERE h.depth = md.max_d
    
    UNION ALL
    
    -- 逐层向上聚合，合并同父节点的所有子节点到同一children数组
    SELECT 
        h.propertyID, 
        h.parentID, 
        h.title,
        h.class,
        h.typeid,
        h.value,
        h.depth,
        JSON_QUERY((
            SELECT 
                r.propertyID, 
                r.title,
                r.class,
                r.typeid,
                r.value,
                JSON_QUERY(r.children) AS children
            FROM Rollup r
            WHERE r.parentID = h.propertyID
            FOR JSON PATH
        )) AS children
    FROM Hierarchy h
    INNER JOIN Rollup r ON h.depth = r.depth - 1
)
-- 最终输出根节点的完整嵌套JSON
SELECT 
    propertyID, 
    title,
    class,
    typeid,
    value,
    JSON_QUERY(IIF(children = '[]', '[{}]', children)) AS children
FROM Rollup
WHERE parentID = 0 AND depth = (SELECT max_d FROM MaxDepth)
FOR JSON PATH

核心调整说明 #

• 新增节点层级计算逻辑，先从叶子节点遍历给所有节点标记深度，规避单条子节点匹配导致的重复生成问题
• 上卷阶段从最深层节点开始，每次聚合当前父节点下的所有子节点到同一个children数组，保证一个父节点仅生成一条记录
• 兼容空children输出格式，自动替换空数组为你要求的[{}]结构

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