如何用SQL查询JSON文件中所有节点及嵌套节点名称？

假设你的表为jsondata，存储JSON内容的字段是json_content，可以通过**递归CTE（公共表表达式）**实现全层级节点的提取，解决仅能获取根节点的问题：

基础实现（含数组索引） #

WITH RecursiveJsonNodes AS (
    -- 提取根节点
    SELECT
        CAST('$.' + [key] AS NVARCHAR(MAX)) AS node_path,
        [key] AS node_name,
        value AS node_value,
        JSON_TYPE(value) AS node_type,
        1 AS level
    FROM jsondata
    CROSS APPLY OPENJSON(json_content)
    UNION ALL
    -- 递归遍历子节点
    SELECT
        CAST(r.node_path + '.' + j.[key] AS NVARCHAR(MAX)) AS node_path,
        j.[key] AS node_name,
        j.value AS node_value,
        JSON_TYPE(j.value) AS node_type,
        r.level + 1 AS level
    FROM RecursiveJsonNodes r
    CROSS APPLY OPENJSON(r.node_value) j
    WHERE JSON_TYPE(r.node_value) IN ('object', 'array') -- 仅对对象/数组继续递归
)
-- 去重后输出唯一节点路径与层级
SELECT DISTINCT
    node_path,
    node_name,
    level
FROM RecursiveJsonNodes
ORDER BY level, node_path;

数组节点优化（统一显示[*]而非索引） #

如果JSON包含数组，上面的查询会把数组索引（如0、1）作为节点名，若要统一显示数组节点标识，可调整递归逻辑：

WITH RecursiveJsonNodes AS (
    SELECT
        CAST('$.' + [key] AS NVARCHAR(MAX)) AS node_path,
        [key] AS node_name,
        value AS node_value,
        JSON_TYPE(value) AS node_type,
        1 AS level
    FROM jsondata
    CROSS APPLY OPENJSON(json_content)
    UNION ALL
    SELECT
        CASE
            WHEN JSON_TYPE(r.node_value) = 'array' THEN CAST(r.node_path + '[*]' AS NVARCHAR(MAX))
            ELSE CAST(r.node_path + '.' + j.[key] AS NVARCHAR(MAX))
        END AS node_path,
        CASE
            WHEN JSON_TYPE(r.node_value) = 'array' THEN '[*]'
            ELSE j.[key]
        END AS node_name,
        j.value AS node_value,
        JSON_TYPE(j.value) AS node_type,
        r.level + 1 AS level
    FROM RecursiveJsonNodes r
    CROSS APPLY OPENJSON(r.node_value) j
    WHERE JSON_TYPE(r.node_value) IN ('object', 'array')
)
SELECT DISTINCT
    node_path,
    node_name,
    level
FROM RecursiveJsonNodes
ORDER BY level, node_path;

性能注意事项 #

针对数十万条JSON数据的场景：

• 可给json_content字段创建JSON非聚集索引（SQL Server 2016+支持），提升解析效率。
• 若无需保留节点值，可在CTE中移除node_value字段，减少内存占用。
• 可分批处理数据，避免一次性递归所有记录导致性能瓶颈。

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