如何用OPENJSON从含多数组的JSON中提取列生成结构化表格

给定如下格式的JSON（多键对应数组，各数组元素数量一致）：

{
"reporting.unit": [ "F-1", "F-2", "F-3"],
"notional.lc": [  100.1, 140.2, 150.3]
}

需要将其转换为行列对应的结构化表格，期望输出：

直接使用OPENJSON解析时，仅能得到每个键对应的数组整体，无法生成结构化数据：

DECLARE @json nvarchar(max);
SELECT @json = '{
"reporting.unit": [ "F-1", "F-2", "F-3"],
"notional.lc": [  100.1, 140.2, 150.3]
}';
SELECT *
FROM OPENJSON (@json);

返回结果：

解决方案 #

核心思路是通过OPENJSON解析每个数组时，获取元素的索引（key字段），再通过索引将不同数组的对应元素关联，从而生成结构化表格。

两列场景实现 #

DECLARE @json nvarchar(max);
SELECT @json = '{
"reporting.unit": [ "F-1", "F-2", "F-3"],
"notional.lc": [  100.1, 140.2, 150.3]
}';

SELECT 
    ru.[value] AS [reporting.unit],
    nl.[value] AS [notional.lc]
FROM 
    OPENJSON(@json, '$.reporting.unit') ru
INNER JOIN
    OPENJSON(@json, '$.notional.lc') nl
ON ru.[key] = nl.[key];

执行后即可得到期望的结构化表格。

扩展到20+列 #

如果有更多列，只需继续添加INNER JOIN解析对应数组，保持按key关联即可。示例如下（新增currency列）：

DECLARE @json nvarchar(max);
SELECT @json = '{
"reporting.unit": [ "F-1", "F-2", "F-3"],
"notional.lc": [ 100.1, 140.2, 150.3],
"currency": [ "USD", "EUR", "GBP"]
}';

SELECT 
    ru.[value] AS [reporting.unit],
    nl.[value] AS [notional.lc],
    curr.[value] AS [currency]
FROM 
    OPENJSON(@json, '$.reporting.unit') ru
INNER JOIN
    OPENJSON(@json, '$.notional.lc') nl ON ru.[key] = nl.[key]
INNER JOIN
    OPENJSON(@json, '$.currency') curr ON ru.[key] = curr.[key];

注意事项 #

• 需确保所有数组的元素数量一致，否则INNER JOIN会丢弃不匹配的行；若要保留所有行，可替换为FULL JOIN。
• 若数组元素是复杂JSON对象，可在OPENJSON中通过WITH子句定义具体字段结构。

内容的提问来源于stack exchange，提问作者Den. A.