SQL解析JSON：如何将shipmentItems数组元素提取为逗号分隔列值

你需要调整语句逻辑，先拆分数组再聚合拼接，修改后的完整语句如下（适用于SQL Server 2017及以上版本）：

DECLARE @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'C:\Users\XPS-LT\json\today\shipments_20211031.json', SINGLE_CLOB) AS IMPORT

SELECT 
    s.shipmentId,
    s.orderNumber,
    s.shipDate,
    s.serviceCode,
    STRING_AGG(item.sku, ',') WITHIN GROUP (ORDER BY item.orderItemId) AS allSkus,
    STRING_AGG(item.quantity, ',') WITHIN GROUP (ORDER BY item.orderItemId) AS allQuantities
FROM OPENJSON (@JSON, '$.shipments') 
WITH 
(
    [shipmentId] bigint, 
    [orderNumber] nvarchar(60), 
    [shipDate] date, 
    [serviceCode] nvarchar(30), 
    [shipmentItems] NVARCHAR(MAX) AS JSON
)  AS s
CROSS APPLY OPENJSON(s.shipmentItems)
WITH (
    sku NVARCHAR(100) '$.sku',
    quantity INT '$.quantity',
    orderItemId BIGINT '$.orderItemId'
) AS item
GROUP BY 
    s.shipmentId,
    s.orderNumber,
    s.shipDate,
    s.serviceCode
;

逻辑说明 #

1. 第一次解析shipments节点时，不再硬编码取数组第一个元素的SKU和数量，而是把完整的shipmentItems数组以JSON格式取出
2. 通过CROSS APPLY搭配第二次OPENJSON，把每个发货单下的所有货品拆分为独立行，取出每行的SKU、数量、订单项ID
3. 按发货单核心字段分组，使用STRING_AGG将同个发货单下的所有SKU、数量分别拼接为逗号分隔的字符串，WITHIN GROUP排序规则保证SKU和数量的顺序一一对应

如果你使用的是SQL Server 2016及更早版本（无内置STRING_AGG函数），可以改用FOR XML PATH实现拼接，拼接部分替换为以下写法即可：

STUFF((SELECT ',' + item2.sku 
       FROM OPENJSON(s.shipmentItems)
       WITH (sku NVARCHAR(100) '$.sku', orderItemId BIGINT '$.orderItemId') AS item2
       ORDER BY item2.orderItemId
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS allSkus,
STUFF((SELECT ',' + CAST(item2.quantity AS NVARCHAR(10))
       FROM OPENJSON(s.shipmentItems)
       WITH (quantity INT '$.quantity', orderItemId BIGINT '$.orderItemId') AS item2
       ORDER BY item2.orderItemId
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS allQuantities

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