如何在SQL中解析表内JSON列并关联其他列（忽略NULL值）

针对[config].[source_test]表中config列的JSON数据（含嵌套结构、部分行值为NULL），可以通过递归提取所有JSON键+动态SQL生成的方式实现全表自动解析，无需硬编码键名。以下是具体实现步骤：

步骤1：递归提取所有唯一JSON键（含嵌套） #

使用递归CTE遍历所有行的JSON数据，提取顶层及嵌套的所有键路径：

WITH RecursiveJSONKeys AS (
    -- 提取顶层JSON键
    SELECT 
        source_id,
        [key] AS JSONKey,
        [value],
        JSON_VALUE([value], '$.type') AS ValueType,
        CAST('$.' + [key] AS NVARCHAR(MAX)) AS Path
    FROM [config].[source_test]
    CROSS APPLY OPENJSON(config)
    WHERE config IS NOT NULL

    UNION ALL

    -- 递归提取嵌套对象的键
    SELECT 
        r.source_id,
        j.[key] AS JSONKey,
        j.[value],
        JSON_VALUE(j.[value], '$.type') AS ValueType,
        CAST(r.Path + '.' + j.[key] AS NVARCHAR(MAX)) AS Path
    FROM RecursiveJSONKeys r
    CROSS APPLY OPENJSON(r.[value]) j
    WHERE JSON_VALUE(r.[value], '$.type') = 'object' -- 仅处理对象类型的嵌套
)
-- 存储所有唯一的JSON键路径到临时表
SELECT DISTINCT Path AS JSONPath
INTO #TempJSONKeys
FROM RecursiveJSONKeys;

步骤2：生成并执行动态SQL #

将提取的键路径拼接为SELECT列表达式，生成动态SQL并执行：

DECLARE @SelectColumns NVARCHAR(MAX);
DECLARE @DynamicSQL NVARCHAR(MAX);

-- 构建解析JSON的列表达式，嵌套键用下划线替换点号作为列名
SELECT @SelectColumns = STRING_AGG(
    CONCAT(
        'JSON_VALUE(t.config, ''', JSONPath, ''') AS ', 
        REPLACE(REPLACE(JSONPath, '$.', ''), '.', '_')
    ),
    ', '
)
FROM #TempJSONKeys;

-- 拼接完整查询语句
SET @DynamicSQL = CONCAT(
    'SELECT t.source_id, t.source_file, t.source_name, t.source_file_path, ',
    ISNULL(@SelectColumns, ''), -- 处理无JSON数据的情况
    ' FROM [config].[source_test] t'
);

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL;

-- 清理临时表
DROP TABLE #TempJSONKeys;

关键说明 #

• 该方案会自动适配全表所有JSON键，新增或修改JSON结构无需修改代码。
• 若JSON包含数组类型值，需额外通过OPENJSON展开数组并关联原表（需根据业务需求调整逻辑）。
• STRING_AGG函数要求SQL Server 2017及以上版本，旧版本可替换为FOR XML PATH拼接字符串：

  SELECT @SelectColumns = STUFF(
      (SELECT ', JSON_VALUE(t.config, ''' + JSONPath + ''') AS ' + REPLACE(REPLACE(JSONPath, '$.', ''), '.', '_')
       FROM #TempJSONKeys
       FOR XML PATH('')),
      1, 1, ''
  );
  

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