SQL Server 2016中用OPENJSON解析JSON并映射列名存入表

刚好我之前处理过类似的动态JSON解析需求，针对你给出的JSON结构，我们可以通过动态SQL结合OPENJSON来实现把columns作为列名，data中的行数据对应插入到表中的需求。下面是具体的实现步骤和代码：

第一步：解析JSON并提取列名和数据 #

首先，我们需要先把JSON字符串中的columns数组提取出来，拼成可用的列名字符串；同时把data中的每一行数组解析成对应的行数据。

假设我们要插入的目标表是YourTargetTable（你可以替换成实际表名），先看完整的代码示例：

DECLARE @json NVARCHAR(MAX) = N'{ 
    "RESULT_1": { 
        "columns": ["col1", "col2", "col3", "col4"], 
        "data": [ 
            ["0", null, "12345", "other"], 
            ["1", "a", "54321", "MA"], 
            ["0", null, "76543", "RI"] 
        ] 
    } 
}';

-- 第一步：提取列名并拼接成逗号分隔的字符串（适配SQL Server 2016）
DECLARE @columns NVARCHAR(MAX);
SELECT @columns = STUFF((SELECT ', ' + QUOTENAME([value])
                         FROM OPENJSON(@json, '$.RESULT_1.columns')
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 第二步：构建动态INSERT语句，解析data中的每一行数据
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'
INSERT INTO YourTargetTable (' + @columns + N')
SELECT ' + 
    -- 动态生成每个列对应的JSON_VALUE提取语句
    STUFF((SELECT ', JSON_VALUE(data_row.value, ''$[' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS NVARCHAR) + N']'')'
           FROM OPENJSON(@json, '$.RESULT_1.columns')
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + N'
FROM OPENJSON(@json, ''$.RESULT_1.data'') AS data_row;';

-- 执行动态SQL
EXEC sp_executesql @sql, N'@json NVARCHAR(MAX)', @json = @json;

关键细节说明 #

• 列名处理：用QUOTENAME包裹列名，避免列名包含特殊字符或关键字导致SQL语法错误；SQL Server 2016不支持STRING_AGG，所以用FOR XML PATH来拼接列名字符串。
• 数据解析：OPENJSON(@json, '$.RESULT_1.data')会把二维数组的每一行解析成一个JSON数组，然后我们通过JSON_VALUE(data_row.value, '$[索引]')来提取每个位置的元素，索引从0开始，完全对应columns数组的顺序。
• NULL值处理：JSON中的null会被JSON_VALUE正确解析为SQL中的NULL，不需要额外转换。

注意事项 #

1. 确保目标表YourTargetTable的列名和columns数组中的列名完全匹配（大小写敏感取决于你的数据库排序规则）。
2. 如果目标表的列有特定数据类型，比如col1是INT类型，需要在提取数据时做类型转换，比如把对应位置的语句改成CAST(JSON_VALUE(data_row.value, '$[0]') AS INT)，这时候需要在动态SQL中针对性调整。

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