SQL Server中解析层级键逐行变化的JSON列方法

需求与问题 #

需要从包含嵌套JSON列的SQL表生成自动化报表：JSON结构中distributionOrders下的键为动态变化的订单号，对应值是包含订单信息的数组，需提取orderNumber、orderType、itemNumber作为报表列。
当前使用OPENJSON+CROSS APPLY的写法需硬编码订单号，仅能处理单行，无法批量处理数百行数据，且不能修改原始JSON结构，寻求可行方案。

示例JSON #

{"distributionOrders":{"3000283984":[{"orderNumber":"3000283984","orderType":"STC","itemNumber":"W01874"}]}}
{"distributionOrders":{"3000308956":[{"orderNumber":"3000308956","orderType":"EVA","itemNumber":"S28741"}]}}
{"distributionOrders":{"3000308961":[{"orderNumber":"3000308961","orderType":"EXP","itemNumber":"W09234"}]}}
{"distributionOrders":{"3000309119":[{"orderNumber":"3000309119","orderType":"STC","itemNumber":"W01874"}]}}

原单行查询（仅作参考） #

SELECT p.orderNumber, p.orderType, p.itemNumber
FROM myDatabase
CROSS APPLY OPENJSON(shipment_details)
WITH (distributionOrders NVARCHAR(max) AS JSON) do
CROSS APPLY OPENJSON(do.distributionOrders)
WITH ("3000325050" NVARCHAR(max) AS JSON)nu
OUTER APPLY OPENJSON(nu."3000325050")
WITH(orderNumber varchar(20), orderType varchar(20), itemNumber varchar(20))p

批量处理解决方案 #

核心思路是遍历distributionOrders下的所有动态键值对，无需硬编码订单号，直接解析对应数组：

SELECT 
    p.orderNumber, 
    p.orderType, 
    p.itemNumber
FROM myDatabase
-- 解析外层JSON，提取distributionOrders节点
CROSS APPLY OPENJSON(shipment_details)
WITH (distributionOrders NVARCHAR(MAX) AS JSON) do
-- 遍历distributionOrders下的所有动态键值对，返回key（订单号）和value（订单数组JSON）
CROSS APPLY OPENJSON(do.distributionOrders) AS nu
-- 解析每个动态键对应的订单数组，提取目标字段
CROSS APPLY OPENJSON(nu.[value])
WITH (
    orderNumber VARCHAR(20),
    orderType VARCHAR(20),
    itemNumber VARCHAR(20)
) p

关键说明

• 第二个OPENJSON不使用WITH子句，自动返回distributionOrders下所有键值对的key和value字段
• 直接用nu.[value]作为JSON数据源，解析出订单数组中的字段
• 该写法可批量处理表中所有行，不受distributionOrders下订单号动态变化的影响

内容的提问来源于stack exchange，提问作者Ty B.