Microsoft SQL Server如何递归获取层级关联子项并输出JSON

要实现不限层级的嵌套子项JSON输出，核心是通过递归逻辑逐层拼接子节点结构，以下是可直接落地的实现方式：

前置修正 #

你提供的初始脚本存在几处SQL Server语法错误，需要先修正才能正常运行：

• items建表语句末尾多了逗号，name字段未指定长度
• kits建表语句两个字段间缺少逗号
• items插入语句字段名写错（写为id，实际字段是item_id），字符串值用了双引号（SQL Server字符串需用单引号）

修正后的初始化脚本：

CREATE TABLE dbo.items (
    item_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE dbo.kits (
    parent_item_id INT NOT NULL REFERENCES dbo.items(item_id),
    child_item_id INT NOT NULL REFERENCES dbo.items(item_id),
    PRIMARY KEY (parent_item_id, child_item_id)
);

INSERT INTO items (item_id, name)
VALUES 
(1, 'Rainy day kit'),
(2, 'Beach day kit'),
(3, 'Umbrella'),
(4, 'Jacket'),
(5, 'Swimsuit'),
(6, 'Sunscreen'),
(7, 'Rainy Beach day kit');

INSERT INTO kits (parent_item_id, child_item_id)
VALUES 
(1, 3),
(1, 4),
(2, 5),
(2, 6),
(7, 1),
(7, 2);

实现步骤 #

1. 创建递归JSON生成函数 #

创建一个标量函数，传入父项ID即可递归返回该节点下所有层级子项的JSON数组。这里必须用JSON_QUERY包裹递归返回的结果，否则FOR JSON会把JSON片段转义为普通字符串：

CREATE OR ALTER FUNCTION dbo.get_nested_children(@parent_item_id INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @children_json NVARCHAR(MAX);
    SELECT @children_json = (
        SELECT 
            i.item_id,
            i.name,
            -- 递归查询当前子项的下级，JSON_QUERY标记为合法JSON避免转义
            JSON_QUERY(dbo.get_nested_children(i.item_id)) AS kit_children
        FROM items i
        INNER JOIN kits k 
            ON k.child_item_id = i.item_id
        WHERE k.parent_item_id = @parent_item_id
        FOR JSON PATH
    );
    -- 如果不需要叶子节点显示空的kit_children数组，直接RETURN @children_json即可
    RETURN ISNULL(@children_json, '[]');
END;

2. 执行查询 #

调用上述函数查询指定条目即可，比如查询item_id=7的条目：

SELECT 
    i.item_id,
    i.name,
    dbo.get_nested_children(i.item_id) AS kit_children
FROM items i
WHERE i.item_id = 7;

如果需要直接返回完整的JSON对象而非表格结构，可以加上FOR JSON配置：

SELECT 
    i.item_id,
    i.name,
    JSON_QUERY(dbo.get_nested_children(i.item_id)) AS kit_children
FROM items i
WHERE i.item_id = 7
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

结果验证 #

执行后返回的结构和预期完全一致：

{
    "item_id": 7,
    "name": "Rainy Beach day kit",
    "kit_children": [
        {
            "item_id": 1,
            "name": "Rainy day kit",
            "kit_children": [
                {"item_id":3, "name":"Umbrella"},
                {"item_id":4, "name":"Jacket"}
            ]
        },
        {
            "item_id": 2,
            "name": "Beach day kit",
            "kit_children": [
                {"item_id":5, "name":"Swimsuit"},
                {"item_id":6, "name":"Sunscreen"}
            ]
        }
    ]
}

注意：如果函数最后去掉ISNULL直接返回@children_json，叶子节点因为没有子项会返回NULL，FOR JSON会自动忽略NULL字段，就不会显示空的kit_children属性，和你给出的期望输出完全匹配。

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