如何使用OPENJSON处理路径不确定的嵌套JSON并提取Property数组

SQL Server 的 OPENJSON 没有内置的全局搜索指定键的功能，但可以通过递归CTE（通用表表达式）遍历JSON所有嵌套节点，无需硬编码路径即可定位任意层级的Property数组，完整实现代码如下：

Declare @Jsonobj as nvarchar(max)
Select @Jsonobj = N'{
    "ID": "StudentInformation",
    "Name": "Student Information",
    "Type": "s_info",
    "Details": [
        "Student Information",
        "Greendale Community College"
    ],
    "Date": "21 October 2021",
    "Rows": [
        {
            "RowType": "Header",
            "Cells": [
                {
                    "Value": ""
                },
                {
                    "Value": "21 Feb 2021"
                },
                {
                    "Value": "22 Aug 2020"
                }
            ]
        },
        {
            "RowType": "Section",
            "Title": "Class",
            "Rows": []
        },
        {
            "RowType": "Section",
            "Title": "Grade",
            "Rows": [
                {
                    "RowType": "Row",
                    "Cells": [
                        {
                            "Value": "5A",
                            "Property": [
                                {
                                    "Id": "1",
                                    "Value": "John Smith"
                                }
                            ]
                        },
                        {
                            "Value": "5A",
                            "Property": [
                                {
                                    "Id": "2",
                                    "Value": "Jane Doe"
                                }
                            ]
                        },
                        {
                            "Value": "5B",
                            "Property": [
                                {
                                    "Id": "1",
                                    "Value": "Ben Frank"
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    ]
}';

WITH RecursiveJsonParse AS (
    -- 递归锚点：解析最外层JSON节点
    SELECT 
        [key] AS node_key,
        [value] AS node_value,
        [type] AS node_type
    FROM OPENJSON(@Jsonobj)
    UNION ALL
    -- 递归拆解所有子节点：只要是对象/数组就继续解析
    SELECT 
        sub.[key] AS node_key,
        sub.[value] AS node_value,
        sub.[type] AS node_type
    FROM RecursiveJsonParse r
    CROSS APPLY OPENJSON(r.node_value) sub
    WHERE r.node_type IN (4, 5) -- 4=数组,5=对象，仅对复合类型继续拆解
)
-- 提取所有Property数组中的Value字段
SELECT JSON_VALUE(v.value, 'strict $.Value') AS Names
FROM RecursiveJsonParse r
CROSS APPLY OPENJSON(r.node_value) v
WHERE r.node_key = 'Property' AND r.node_type = 4 -- 匹配所有名为Property的数组

代码说明 #

• 递归CTE会逐层遍历JSON的所有嵌套节点，不受Property数组所在层级限制
• OPENJSON返回的type字段用于判断节点类型：4代表数组、5代表对象，仅对这两类复合节点继续递归拆解
• 最终过滤条件node_key = 'Property' AND node_type = 4会命中所有符合要求的数组，解析后输出的结果和原有硬编码路径的执行结果完全一致。

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