MS SQL 中提取全部嵌套JSON键值对的最优实现方法

核心解决方案：使用OPENJSON表值函数 #

你之前用到的逐字段写JSON_VALUE的方案属于硬编码实现，仅适合固定结构的JSON场景，要适配动态键、多字段、嵌套结构的话，直接用SQL Server内置的OPENJSON函数即可，无需手动罗列每个键，后续JSON新增字段也能自动识别提取。

1. 提取顶层全部键值对 #

如果你的JSON只有一级结构，直接调用即可返回所有键值对：

SELECT 
    j.[key] AS 键名,
    j.[value] AS 键值,
    j.[type] AS 数据类型标识 -- 0=Null/1=字符串/2=数值/3=布尔/4=数组/5=对象
FROM 你的表名 f
CROSS APPLY OPENJSON(f.doc) j
-- 可加WHERE条件筛选指定行

2. 递归提取所有层级键值对（支持嵌套结构） #

如果JSON包含多层嵌套（比如你示例中的address.city这类层级），用递归CTE即可遍历所有层级的键：

WITH RecursiveJSON AS (
    -- 锚点成员：提取顶层键
    SELECT
        CAST(j.[key] AS NVARCHAR(MAX)) AS 完整键路径,
        j.[value],
        j.[type]
    FROM 你的表名 f
    CROSS APPLY OPENJSON(f.doc) j
    -- 这里加WHERE条件筛选你要处理的行，不需要就删

    UNION ALL

    -- 递归成员：遍历嵌套的对象/数组
    SELECT
        CAST(r.完整键路径 + N'.' + j.[key] AS NVARCHAR(MAX)),
        j.[value],
        j.[type]
    FROM RecursiveJSON r
    CROSS APPLY OPENJSON(r.[value]) j
    WHERE r.[type] IN (4,5) -- 仅对数组、对象类型的值做递归拆解
)
-- 最终输出所有标量类型的键值对
SELECT 完整键路径, 键值
FROM RecursiveJSON
WHERE [type] NOT IN (4,5) -- 过滤掉数组、对象本身，只返回最终的字段值

3. 动态生成宽表（无需手动写列） #

如果你需要返回和JSON_VALUE写法一致的、每个键作为单独列的宽表，用动态SQL拼接即可，不用手动输入20+个字段：

DECLARE @columnList NVARCHAR(MAX), @query NVARCHAR(MAX)

-- 第一步：获取所有唯一的键路径，作为宽表的列
SELECT @columnList = STRING_AGG(QUOTENAME(完整键路径), N', ')
FROM (
    SELECT DISTINCT 完整键路径
    FROM RecursiveJSON -- 这里替换为你上面递归CTE的查询逻辑
) AS AllKeys

-- 第二步：拼接PIVOT转宽表的SQL
SET @query = N'
SELECT *
FROM (
    SELECT 完整键路径, 键值
    FROM RecursiveJSON
) AS SourceData
PIVOT (
    MAX(键值)
    FOR 完整键路径 IN (' + @columnList + N')
) AS PivotResult'

-- 执行动态查询
EXEC sp_executesql @query

SSMS使用建议 #

• 临时查看JSON内容：直接点击查询结果中JSON字段的单元格，SSMS会自动弹出格式化后的JSON预览标签页，和Azure Data Studio的查看体验一致。
• 生产环境固定逻辑：优先用递归OPENJSON方案，无需随JSON结构变更修改代码，新增字段会自动被提取。

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