SQL Server递归解析嵌套JSON数组，生成带父ID的结构化表

你提到的递归CTE思路完全正确，只是递归成员的逻辑没处理到位——我们需要在递归时解析父节点的children数组，同时把父节点的propertyID传递给子节点作为parentID。下面是可以直接运行的完整解决方案：

declare @json nvarchar(max) = '{ "propertyObjects": [{ "propertyID": 1 , "title": "foo" , "class": "" , "typeid": 150 , "value": "bar" , "children": [{}] }, { "propertyID": 2 , "title": "foo" , "class": "" , "typeid": 128 , "value": "bar" , "children": [{}] }, { "propertyID": 3 , "title": "foo" , "class": "" , "typeid": 128 , "value": "bar" , "children": [{ "propertyID": 4 , "title": "foo" , "class": "" , "typeid": 128 , "value": "bar" , "children": [{}] }, { "propertyID": 5 , "title": "foo" , "class": "" , "typeid": 128 , "value": "bar" , "children": [{}] }, { "propertyID": 6 , "title": "foo" , "class": "" , "typeid": 128 , "value": "bar" , "children": [{ "propertyID": 7 , "title": "foo" , "class": "" , "typeid": 128 , "value": "bar" , "children": [{ "propertyID": 8 , "title": "foo" , "class": "" , "typeid": 128 , "value": "bar" , "children": [{}] }] }] }] }] }'

;with cte as (
    -- 锚点成员：提取顶层对象，父ID固定为0
    select 
        p.propertyID,
        0 as parentID,
        p.title,
        p.typeid,
        p.[value],
        p.children
    from openjson(@json, '$.propertyObjects')
    with (
        propertyID int,
        title nvarchar(100),
        typeid int,
        [value] nvarchar(1000),
        children nvarchar(max) as JSON
    ) as p

    union all

    -- 递归成员：解析父节点的children数组，传递父ID
    select 
        c.propertyID,
        parent.propertyID as parentID, -- 用父节点的propertyID作为当前节点的父ID
        c.title,
        c.typeid,
        c.[value],
        c.children
    from cte as parent
    cross apply openjson(parent.children) -- 解析父节点的children数组
    with (
        propertyID int,
        title nvarchar(100),
        typeid int,
        [value] nvarchar(1000),
        children nvarchar(max) as JSON
    ) as c
    -- 过滤空的children对象，避免无效递归
    where c.propertyID is not null
)
-- 最终只返回需要的字段，去掉children列
select propertyID, parentID, title, typeid, [value]
from cte
order by propertyID

关键逻辑说明： #

1. 锚点成员：直接从$.propertyObjects路径提取顶层对象，parentID设为0，同时保留children字段（标记为JSON类型）供递归使用。
2. 递归成员：
   • 关联CTE中的父节点（cte as parent）
   • 用CROSS APPLY OPENJSON(parent.children)解析父节点的子对象数组
   • 将父节点的propertyID赋值给子节点的parentID，完成父子关联
   • 加了where c.propertyID is not null过滤掉JSON里的空对象（比如children: [{}]这种没有有效propertyID的条目）
3. 最终查询：只返回你需要的字段，去掉中间用的children列，按propertyID排序后就和你期望的结果完全一致。

为什么你之前的尝试失败？ #

• 递归成员里你写了固定值（0 as propertyID等），没有实际解析父节点的children数组，自然无法获取子对象的数据
• 没有传递父节点的propertyID作为子节点的parentID，导致父子关联丢失
• 用CROSS APPLY的方案只能处理固定层级的嵌套，无法应对不确定深度的结构，而递归CTE可以自动遍历所有层级

运行上面的代码后，你会得到完全符合预期的结构化结果：

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