如何将嵌套JSON数据导入SQL Server 2012并映射列与行值

既然你用的是SQL Server 2012，得注意它不像2016及以后版本有原生的JSON解析函数，所以得用OPENXML结合XML转换的方式来处理这种嵌套JSON。下面是一套完整的解决方案，能自动把columns里的列名和rows里的对应值匹配起来，输出你想要的结构化结果：

完整代码示例 #

DECLARE @Json NVARCHAR(MAX) = N'{ "tables": [ { "name": "PrimaryResult", "columns": [ { "name": "timestamp", "type": "datetime" }, { "name": "id", "type": "string" }, { "name": "name", "type": "string" }, { "name": "url", "type": "string" }, { "name": "duration", "type": "real" } ], "rows": [ [ "2019-04-08T13:09:52.871Z", "244", "Internal", "https://google.com", 1245 ] ] } ] }';

-- 将JSON转换为XML格式（SQL Server 2012仅支持XML解析，需此转换）
DECLARE @Xml XML;
SET @Xml = CAST(N'<root>' + 
    REPLACE(REPLACE(REPLACE(REPLACE(@Json, N'{', N'<node>'), N'}', N'</node>'), N':', N'>'), N',', N'</node><node>') + 
N'</root>' AS XML);

-- 准备XML文档句柄
DECLARE @XmlHandle INT;
EXEC sp_xml_preparedocument @XmlHandle OUTPUT, @Xml;

-- 提取columns中的列名（SQL Server 2012无STRING_AGG，用FOR XML PATH拼接）
DECLARE @Columns NVARCHAR(MAX);
SET @Columns = STUFF((
    SELECT N',' + QUOTENAME(c.name)
    FROM OPENXML(@XmlHandle, N'/root/node/node/node', 2)
    WITH (
        name NVARCHAR(100) N'./node[1]/text()'
    ) c
    WHERE EXISTS (
        SELECT 1 
        FROM OPENXML(@XmlHandle, N'/root/node/node', 2)
        WITH (
            nodeName NVARCHAR(100) N'local-name(.)'
        ) tbl
        WHERE tbl.nodeName = N'columns'
    )
    ORDER BY (SELECT NULL)
    FOR XML PATH(''), TYPE
).value('.', NVARCHAR(MAX)), 1, 1, N'');

-- 提取rows中的数据，并标记行索引和列索引
DECLARE @Rows TABLE (RowIndex INT, Value NVARCHAR(MAX), ColumnIndex INT);
INSERT INTO @Rows
SELECT 
    DENSE_RANK() OVER(ORDER BY r.parentnode) AS RowIndex,
    v.value('.', NVARCHAR(MAX)) AS Value,
    ROW_NUMBER() OVER(PARTITION BY r.parentnode ORDER BY (SELECT NULL)) AS ColumnIndex
FROM OPENXML(@XmlHandle, N'/root/node/node/node/node', 2) r
CROSS APPLY r.nodes('.') AS t(v)
WHERE EXISTS (
    SELECT 1 
    FROM OPENXML(@XmlHandle, N'/root/node/node', 2)
    WITH (
        nodeName NVARCHAR(100) N'local-name(.)'
    ) tbl
    WHERE tbl.nodeName = N'rows'
);

-- 动态生成PIVOT查询，将行数据转成结构化列
DECLARE @PivotQuery NVARCHAR(MAX);
SET @PivotQuery = N'
SELECT ' + @Columns + N'
FROM (
    SELECT 
        r.RowIndex,
        c.name AS ColumnName,
        r.Value
    FROM @Rows r
    JOIN (
        SELECT 
            name,
            ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ColumnIndex
        FROM OPENXML(@XmlHandle, N''/root/node/node/node'', 2)
        WITH (
            name NVARCHAR(100) N''./node[1]/text()''
        )
        WHERE EXISTS (
            SELECT 1 
            FROM OPENXML(@XmlHandle, N''/root/node/node'', 2)
            WITH (
                nodeName NVARCHAR(100) N''local-name(.)''
            ) tbl
            WHERE tbl.nodeName = N''columns''
        )
    ) c ON r.ColumnIndex = c.ColumnIndex
) src
PIVOT (
    MAX(Value) FOR ColumnName IN (' + @Columns + N')
) pvt;';

-- 执行动态查询
EXEC sp_executesql @PivotQuery, N'@XmlHandle INT', @XmlHandle;

-- 释放XML文档资源
EXEC sp_xml_removedocument @XmlHandle;

关键步骤说明 #

1. JSON转XML：因为SQL Server 2012没有原生JSON支持，我们先把JSON转换成XML结构，让OPENXML可以解析它。
2. 提取列名：从转换后的XML中解析columns节点的name属性，用FOR XML PATH拼接成列名字符串（兼容2012版本）。
3. 提取行数据：解析rows节点的数组值，标记每个值对应的行索引和列索引，方便后续匹配。
4. 动态PIVOT：用动态SQL生成PIVOT查询，将行数据转成以columns命名的结构化列，最终输出你需要的表格结果。

执行这段代码后，你会得到如下结果：

（注：timestamp是SQL Server保留关键字，代码中用QUOTENAME包裹避免语法错误）

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