如何使用FOR JSON PATH生成5级嵌套JSON数组？

问题背景 #

需要生成最多5级深度的家族树嵌套JSON，当前仅能通过FOR JSON PATH实现2级深度。由于FOR JSON AUTO无法自定义字段命名和输出格式，必须基于FOR JSON PATH实现多层嵌套，同时保持输出结构的可控性。

示例数据表结构 #

CREATE TABLE [FamilyTree]( 
   [ID] INT NOT NULL ,
   [Name] VARCHAR(250) NOT NULL,
   [ParentID] INT NOT NULL,
) ON [PRIMARY] 
GO

INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(1,N'Person1',0)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(2,N'Person2',0)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(3,N'Person3',1)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(4,N'Person4',2)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(5,N'Person5',3)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(6,N'Person6',3)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(7,N'Person7',4)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(8,N'Person8',4)
INSERT [FamilyTree]([ID],[Name],[ParentID])
VALUES(9,N'Person9',4)

当前2级深度查询及输出 #

当前查询语句：

SELECT
FT1.Name AS [name],
(SELECT Name FROM FamilyTree WHERE ParentID = FT1.ID FOR JSON PATH) children
FROM FamilyTree FT1
WHERE FT1.ParentID = 0
FOR JSON PATH

当前输出：

[
    {
        "name": "Person1",
        "children": [
            {
                "Name": "Person3"
            }
        ]
    },
    {
        "name": "Person2",
        "children": [
            {
                "Name": "Person4"
            }
        ]
    }
]

期望的5级嵌套输出样式 #

[
    {
        "name": "Person1",
        "children": [
            {
                "name": "Person3",
                "children": [
                    {
                        "name": "Person5"
                    },
                    {
                        "name": "Person6"
                    }
                ]
            }
        ]
    },
    {
        "name": "Person2",
        "children": [
            {
                "name": "Person4",
                "children": [
                    {
                        "name": "Person7"
                    },
                    {
                        "name": "Person8"
                    },
                    {
                        "name": "Person9"
                    }
                ]
            }
        ]
    }
]

解决方案 #

方法1：递归CTE + FOR JSON PATH（支持任意层级） #

通过递归CTE遍历整个家族树的层级关系，再结合嵌套的FOR JSON PATH查询生成嵌套JSON，这种方法可以灵活适配1到N级的深度需求，无需手动修改查询结构。

实现代码：

WITH RecursiveFamilyTree AS (
    -- 锚点成员：顶层节点（ParentID=0）
    SELECT 
        ID,
        Name,
        ParentID,
        1 AS Level
    FROM FamilyTree
    WHERE ParentID = 0

    UNION ALL

    -- 递归成员：遍历子节点
    SELECT 
        ft.ID,
        ft.Name,
        ft.ParentID,
        rft.Level + 1 AS Level
    FROM FamilyTree ft
    INNER JOIN RecursiveFamilyTree rft ON ft.ParentID = rft.ID
    WHERE rft.Level < 5 -- 限制最大深度为5级
)
-- 生成嵌套JSON的主查询
SELECT 
    Name AS [name],
    (
        SELECT 
            Name AS [name],
            (
                SELECT 
                    Name AS [name],
                    (
                        SELECT 
                            Name AS [name],
                            (
                                SELECT Name AS [name] 
                                FROM RecursiveFamilyTree ft4 
                                WHERE ft4.ParentID = ft3.ID AND ft4.Level = 5 
                                FOR JSON PATH
                            ) AS [children]
                        FROM RecursiveFamilyTree ft3 
                        WHERE ft3.ParentID = ft2.ID AND ft3.Level = 4 
                        FOR JSON PATH
                    ) AS [children]
                FROM RecursiveFamilyTree ft2 
                WHERE ft2.ParentID = ft1.ID AND ft2.Level = 3 
                FOR JSON PATH
            ) AS [children]
        FROM RecursiveFamilyTree ft2 
        WHERE ft2.ParentID = ft1.ID AND ft2.Level = 2 
        FOR JSON PATH
    ) AS [children]
FROM RecursiveFamilyTree ft1
WHERE ft1.Level = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

方法2：手动嵌套子查询（固定5级深度） #

如果确定只需要5级深度，可以直接通过多层嵌套子查询的方式，逐层定义每个层级的children字段，完全控制每个节点的命名和结构。

实现代码：

SELECT
    FT1.Name AS [name],
    (
        SELECT
            FT2.Name AS [name],
            (
                SELECT
                    FT3.Name AS [name],
                    (
                        SELECT
                            FT4.Name AS [name],
                            (
                                SELECT FT5.Name AS [name] 
                                FROM FamilyTree FT5 
                                WHERE FT5.ParentID = FT4.ID 
                                FOR JSON PATH
                            ) AS [children]
                        FROM FamilyTree FT4 
                        WHERE FT4.ParentID = FT3.ID 
                        FOR JSON PATH
                    ) AS [children]
                FROM FamilyTree FT3 
                WHERE FT3.ParentID = FT2.ID 
                FOR JSON PATH
            ) AS [children]
        FROM FamilyTree FT2 
        WHERE FT2.ParentID = FT1.ID 
        FOR JSON PATH
    ) AS [children]
FROM FamilyTree FT1
WHERE FT1.ParentID = 0
FOR JSON PATH;

说明 #

• 两种方法都能确保输出的JSON字段命名统一为name（符合期望格式），避免了FOR JSON AUTO的自动命名问题。
• 递归CTE方法更灵活，若后续需要调整深度，只需修改WHERE rft.Level < N中的N值即可；手动嵌套方法则更直观，适合固定深度的场景。

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