使用SQL OPENJSON无法读取全部JSON数组数据的问题求助

处理以下JSON字符串时无法返回全部数组数据：

'{"ShipmentData": [{"consignee_name":"Test Sender","consignee_contact_number":"(+852)465456456","consignee_address":"Caloocan, Metro Manila, Philippines, 456 Apartment","consignee_3level_location":"PHILIPPINES METRO MANILA CALOOCAN","consignee_city_name":"CALOOCAN","service_type":"Express","consignee_postal_code":"2345345345","shipment_tracking_id":"QC0123102700001-001","senderVATNumber":null,"recipientVATNumber":null,"instructions":null,"volume_weight":0.2000,"gross_weight":1.0000,"shipment_reference_id":"[\"\"]","package_id":"a1c5664b-09db-4973-a947-33af15d0f030","deliveryDuties":"Tax Paid by: Sender / Receiver","nonDeliveryInstructions":"Return     /     Destroy","sender_name":"Test Consignee","sender_contact_number":"(+63)54554","sender_address":"HongKong, Hong Kong, 123 Apartment","sender_3level_location":"HONG KONG HONG KONG HONG KONG","hub_code":null,"vendor_code":null,"sort_code":"Sort Code: - ATL "}, "CommodityData": [{"quantity":1,"commodity_name":"DOCUMENT"}], [{"quantity":2,"commodity_name":"DOCUMENT2"}]}'

预期结果：

consignee_name | quantity  | SHIFT_ID
------------------------------------
Test Sender    | 1         | DOCUMENT
Test Sender    | 2         | DOCUMENT2

尝试代码：

declare @json_string nvarchar(max) =  '{"ShipmentData": [{"consignee_name":"Test Sender","consignee_contact_number":"(+852)465456456","consignee_address":"Caloocan, Metro Manila, Philippines, 456 Apartment","consignee_3level_location":"PHILIPPINES METRO MANILA CALOOCAN","consignee_city_name":"CALOOCAN","service_type":"Express","consignee_postal_code":"2345345345","shipment_tracking_id":"QC0123102700001-001","senderVATNumber":null,"recipientVATNumber":null,"instructions":null,"volume_weight":0.2000,"gross_weight":1.0000,"shipment_reference_id":"[\"\"]","package_id":"a1c5664b-09db-4973-a947-33af15d0f030","deliveryDuties":"Tax Paid by: Sender / Receiver","nonDeliveryInstructions":"Return     /     Destroy","sender_name":"Test Consignee","sender_contact_number":"(+63)54554","sender_address":"HongKong, Hong Kong, 123 Apartment","sender_3level_location":"HONG KONG HONG KONG HONG KONG","hub_code":null,"vendor_code":null,"sort_code":"Sort Code: - ATL "}, "CommodityData": [{"quantity":1,"commodity_name":"DOCUMENT"}], [{"quantity":2,"commodity_name":"DOCUMENT2"}]}'

select
    sd.consignee_name,
    cd.quantity,
    cd.commodity_name
from 
    OPENJSON(@json_string, '$.ShipmentData')
WITH (
    consignee_name NVARCHAR(100) '$.consignee_name'
) AS sd
CROSS APPLY
    OPENJSON(@json_string, '$.CommodityData')
WITH (
    quantity INT '$.quantity',
    commodity_name NVARCHAR(100) '$.commodity_name'
) AS cd

上述代码仅返回一行CommodityData数据，需解决该问题。

问题根源 #

1. JSON格式非法：原JSON将CommodityData错误嵌套在ShipmentData数组内，且商品数据被拆分为两个独立数组，导致解析时无法识别完整的商品列表。
2. 路径定位错误：错误的JSON结构使得$.CommodityData路径无法正确指向所有商品数据。

修正步骤 #

1. 修复JSON结构：调整为合法格式，将CommodityData与ShipmentData设为同级，同时将所有商品对象合并到一个数组中：

'{"ShipmentData": [{"consignee_name":"Test Sender","consignee_contact_number":"(+852)465456456","consignee_address":"Caloocan, Metro Manila, Philippines, 456 Apartment","consignee_3level_location":"PHILIPPINES METRO MANILA CALOOCAN","consignee_city_name":"CALOOCAN","service_type":"Express","consignee_postal_code":"2345345345","shipment_tracking_id":"QC0123102700001-001","senderVATNumber":null,"recipientVATNumber":null,"instructions":null,"volume_weight":0.2000,"gross_weight":1.0000,"shipment_reference_id":"[\"\"]","package_id":"a1c5664b-09db-4973-a947-33af15d0f030","deliveryDuties":"Tax Paid by: Sender / Receiver","nonDeliveryInstructions":"Return     /     Destroy","sender_name":"Test Consignee","sender_contact_number":"(+63)54554","sender_address":"HongKong, Hong Kong, 123 Apartment","sender_3level_location":"HONG KONG HONG KONG HONG KONG","hub_code":null,"vendor_code":null,"sort_code":"Sort Code: - ATL "}], "CommodityData": [{"quantity":1,"commodity_name":"DOCUMENT"}, {"quantity":2,"commodity_name":"DOCUMENT2"}]}'

2. 修正SQL代码：使用正确的JSON路径解析，同时调整列名匹配预期结果：

declare @json_string nvarchar(max) =  '{"ShipmentData": [{"consignee_name":"Test Sender","consignee_contact_number":"(+852)465456456","consignee_address":"Caloocan, Metro Manila, Philippines, 456 Apartment","consignee_3level_location":"PHILIPPINES METRO MANILA CALOOCAN","consignee_city_name":"CALOOCAN","service_type":"Express","consignee_postal_code":"2345345345","shipment_tracking_id":"QC0123102700001-001","senderVATNumber":null,"recipientVATNumber":null,"instructions":null,"volume_weight":0.2000,"gross_weight":1.0000,"shipment_reference_id":"[\"\"]","package_id":"a1c5664b-09db-4973-a947-33af15d0f030","deliveryDuties":"Tax Paid by: Sender / Receiver","nonDeliveryInstructions":"Return     /     Destroy","sender_name":"Test Consignee","sender_contact_number":"(+63)54554","sender_address":"HongKong, Hong Kong, 123 Apartment","sender_3level_location":"HONG KONG HONG KONG HONG KONG","hub_code":null,"vendor_code":null,"sort_code":"Sort Code: - ATL "}], "CommodityData": [{"quantity":1,"commodity_name":"DOCUMENT"}, {"quantity":2,"commodity_name":"DOCUMENT2"}]}'

select
    sd.consignee_name,
    cd.quantity,
    cd.commodity_name as SHIFT_ID
from 
    OPENJSON(@json_string, '$.ShipmentData')
WITH (
    consignee_name NVARCHAR(100) '$.consignee_name'
) AS sd
CROSS APPLY
    OPENJSON(@json_string, '$.CommodityData')
WITH (
    quantity INT '$.quantity',
    commodity_name NVARCHAR(100) '$.commodity_name'
) AS cd

说明 #

修复后的JSON结构符合标准格式，CROSS APPLY会将发货信息与所有商品数据关联，最终返回两行符合预期的结果，同时通过as SHIFT_ID对齐了预期结果的列名。

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