SQL Server如何解析未知层级父子结构JSON并列出所有对象？

你遇到的CTE报错问题，主要是因为递归部分处理child列时没考虑空JSON数组/对象的情况，而且原CTE的关联逻辑也有优化空间。下面是修正后的解决方案，能完美处理任意深度的父子结构：

修正后的CTE代码 #

declare @json nvarchar(max) = ' { "topparent": [{ "id": "1" , "parent": "0" , "child": [{}] }, { "id": "2" , "parent": "0" , "child": [{}] }, { "id": "3" , "parent": "0" , "child": [{ "id": "4" , "parent": "3" , "child": [{ "id": "5" , "parent": "4" , "child": [{ "id": "6" , "parent": "5" , "child": [{}] }] }] }] }, { "id": "7" , "parent": "0" , "child": [{}] }, { "id": "8" , "parent": "0" , "child": [{}] }, { "id": "9" , "parent": "0" , "child": [{}] }] }'

;with cte as (
    -- 初始节点：解析顶层父节点
    select 
        cast(p.id as int) as id,
        cast(p.parent as int) as parent,
        p.child
    from openjson(@json, '$.topparent') 
    with (
        id nvarchar(10),
        parent nvarchar(10),
        child nvarchar(max) as JSON
    ) p
    where p.id is not null  -- 过滤无有效ID的空节点

    union all

    -- 递归解析子节点
    select 
        cast(r.id as int) as id,
        cast(r.parent as int) as parent,
        r.child
    from cte e
    cross apply openjson(e.child)  -- 用cross apply替代inner join，适配空JSON场景
    with (
        id nvarchar(10),
        parent nvarchar(10),
        child nvarchar(max) as JSON
    ) r
    where r.id is not null  -- 跳过空的子对象，避免无效递归
)
select id, parent from cte;

原代码报错原因分析 #

你的原CTE使用inner join openjson(e.child)...，当e.child是[{}]这类空JSON数组时，解析不到有效数据会导致关联失败，触发"The multi-part identifier 'e.child' could not be bound"错误。另外，直接将JSON中的字符串类型id/parent定义为int可能引发隐式转换问题，先转成nvarchar再转int更安全。

方案优势 #

• 支持任意层级深度的嵌套结构，不管子节点嵌套多少层都能遍历到所有有效对象
• 性能远优于游标：CTE递归是基于集合的操作，SQL Server对其优化更充分
• 自动过滤空节点：跳过[{}]这类无实际数据的子对象，避免无效计算

执行代码后会得到所有有效节点的结果：

id | parent
---|-------
1  | 0
2  | 0
3  | 0
7  | 0
8  | 0
9  | 0
4  | 3
5  | 4
6  | 5

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