如何在SQL Server 2019生成符合D3.js树形图要求的JSON

要避免手动修改JSON的步骤，核心是放弃FOR JSON AUTO的自动映射逻辑，改用FOR JSON PATH手动定义JSON结构，让每一层级的子节点都统一命名为children。以下提供两种适配方案，根据你的实际查询场景选择：

方案一：递归CTE适配任意层级（推荐） #

如果你的节点层级是动态的（虽当前支持最多6级，但递归方式更灵活），可以用递归CTE先统一获取所有节点的层级关系，再嵌套生成children结构：

-- 递归CTE获取所有节点的层级关系
WITH RecursiveNodes AS (
    -- 锚点：根节点（根据你的实际表结构调整根节点判断条件，比如parent_id=0或NULL）
    SELECT 
        node_id AS id,
        node_name AS name,
        parent_node_id AS parent_id
    FROM YourNodeTable
    WHERE parent_node_id IS NULL
    UNION ALL
    -- 递归获取子节点
    SELECT 
        t.node_id AS id,
        t.node_name AS name,
        t.parent_node_id AS parent_id
    FROM YourNodeTable t
    INNER JOIN RecursiveNodes rn ON t.parent_node_id = rn.id
)
-- 嵌套生成带统一children的JSON结构
SELECT 
    id,
    name,
    -- 生成第二级children
    (
        SELECT 
            id,
            name,
            -- 生成第三级children
            (
                SELECT 
                    id,
                    name,
                    -- 生成第四级children
                    (
                        SELECT 
                            id,
                            name,
                            -- 生成第五级children
                            (
                                SELECT 
                                    id,
                                    name,
                                    -- 生成第六级children
                                    (SELECT id, name FROM RecursiveNodes WHERE parent_id = rn4.id FOR JSON PATH) AS children
                                FROM RecursiveNodes rn4 WHERE parent_id = rn3.id FOR JSON PATH
                            ) AS children
                        FROM RecursiveNodes rn3 WHERE parent_id = rn2.id FOR JSON PATH
                    ) AS children
                FROM RecursiveNodes rn2 WHERE parent_id = rn1.id FOR JSON PATH
            ) AS children
        FROM RecursiveNodes rn1 WHERE parent_id = root.id FOR JSON PATH
    ) AS children
FROM RecursiveNodes root
WHERE parent_id IS NULL -- 仅输出根节点，构建完整树形
FOR JSON PATH, ROOT('root'); -- ROOT可选，根据D3所需的顶层结构调整

方案二：固定6级关联适配原查询结构 #

如果你坚持使用原有的l1至l6层级关联逻辑，只需在每一层的子查询中把结果别名设为children即可：

SELECT
    l1.node_id AS id,
    l1.node_name AS name,
    -- 第二级节点，别名children
    (
        SELECT
            l2.node_id AS id,
            l2.node_name AS name,
            -- 第三级节点，别名children
            (
                SELECT
                    l3.node_id AS id,
                    l3.node_name AS name,
                    -- 第四级节点，别名children
                    (
                        SELECT
                            l4.node_id AS id,
                            l4.node_name AS name,
                            -- 第五级节点，别名children
                            (
                                SELECT
                                    l5.node_id AS id,
                                    l5.node_name AS name,
                                    -- 第六级节点，别名children
                                    (SELECT l6.node_id AS id, l6.node_name AS name FROM l6 WHERE l6.parent_id = l5.id FOR JSON PATH) AS children
                                FROM l5 WHERE l5.parent_id = l4.id FOR JSON PATH
                            ) AS children
                        FROM l4 WHERE l4.parent_id = l3.id FOR JSON PATH
                    ) AS children
                FROM l3 WHERE l3.parent_id = l2.id FOR JSON PATH
            ) AS children
        FROM l2 WHERE l2.parent_id = l1.id FOR JSON PATH
    ) AS children
FROM l1
FOR JSON PATH;

关键说明 #

• FOR JSON PATH允许你完全自定义JSON的键名和结构，而FOR JSON AUTO会根据表/别名自动生成键，这是导致l2、l3等命名的核心原因。
• 若节点需要其他属性（如用于树形图的value、size），只需在对应的SELECT语句中添加字段即可，保持各层级结构一致。
• 两种方案生成的JSON中，所有子节点数组都会统一命名为children，完全符合D3.js树形图的要求，无需手动修改。

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