SQL Server中如何用FOR JSON PATH从带ParentID的键值表生成嵌套JSON

要实现将带ParentID的递归键值表转换为指定格式的嵌套JSON，由于原生FOR JSON PATH没有内置递归结构生成能力，我们可以配合递归CTE先逐层生成JSON片段，最后拼接得到完整结果。

完整实现代码 #

-- 测试数据（你提供的建表和插入语句）
create table #temp
(
    [Id] int, 
    [Key] nvarchar(100),
    [Value] nvarchar(max),
    [ParentId] int
)

insert into #temp select 1,'help','',null
insert into #temp select 2,'info','',1
insert into #temp select 3,'contact','example',1
insert into #temp select 4,'SSS','',1
insert into #temp select 5,'title','example',2
insert into #temp select 6,'text','example',2
insert into #temp select 7,'title','example',4
insert into #temp select 8,'text','',4
insert into #temp select 9,'0','',8
insert into #temp select 10,'1','',8
insert into #temp select 11,'title','example',9
insert into #temp select 12,'text','example',9
insert into #temp select 13,'title','example',10
insert into #temp select 14,'text','example',10
GO

-- 递归生成JSON的核心逻辑
WITH RecursiveJson AS (
    -- 锚点：处理最底层无子女的叶子节点
    SELECT 
        t.Id,
        t.[Key],
        t.Value,
        t.ParentId,
        CASE 
            WHEN t.Value <> '' THEN CONCAT(QUOTENAME(t.[Key], '"'), ':', QUOTENAME(t.Value, '"'))
            ELSE NULL
        END AS JsonFragment
    FROM #temp t
    WHERE NOT EXISTS (SELECT 1 FROM #temp WHERE ParentId = t.Id)

    UNION ALL

    -- 递归向上处理所有父节点
    SELECT 
        p.Id,
        p.[Key],
        p.Value,
        p.ParentId,
        CASE 
            -- 判断子节点是否为数字序号，是则生成数组结构
            WHEN EXISTS (
                SELECT 1 FROM #temp c 
                WHERE c.ParentId = p.Id 
                AND TRY_CAST(c.[Key] AS INT) IS NOT NULL
            ) THEN
                CONCAT(
                    QUOTENAME(p.[Key], '"'),
                    ':[',
                    STRING_AGG(CONCAT('{', c.JsonFragment, '}'), ',') WITHIN GROUP (ORDER BY CAST(c.[Key] AS INT)),
                    ']'
                )
            -- 普通子节点生成对象结构
            ELSE
                CONCAT(
                    QUOTENAME(p.[Key], '"'),
                    ':{',
                    STRING_AGG(c.JsonFragment, ','),
                    '}'
                )
        END AS JsonFragment
    FROM #temp p
    JOIN RecursiveJson c ON p.Id = c.ParentId
    GROUP BY p.Id, p.[Key], p.Value, p.ParentId
)
-- 输出根节点的完整JSON
SELECT CONCAT('{', JsonFragment, '}') AS NestedJson
FROM RecursiveJson
WHERE ParentId IS NULL
-- 嵌套深度超过100时取消递归限制
-- OPTION (MAXRECURSION 0)

逻辑说明 #

1. 递归锚点：从树结构最底层的叶子节点开始处理，叶子节点没有子节点，直接生成"key":"value"格式的JSON片段
2. 递归向上遍历：逐层向上处理每个父节点
   • 如果父节点的子节点Key都是数字，判定为数组结构，将子节点片段按数字排序后用[]包裹
   • 其他情况判定为对象结构，将子节点片段用{}包裹
3. 根节点输出：最后取出无父节点的根节点片段，外层补{}得到完整JSON

兼容提示 #

如果使用SQL Server 2017以下版本，不支持STRING_AGG函数，可以替换为FOR XML PATH方式实现字符串拼接：

STUFF((
    SELECT ',' + c.JsonFragment 
    FROM RecursiveJson c 
    WHERE c.ParentId = p.Id 
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

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