Extract a json array items to a single column separated with comma-CodePudding

Struggling to find an answer to the below JSON problem. I would like to display the entire SKU/Quantity list in the "shipmentItems" array to their respective column with a comma-separated value. My example below only allows me to display the first SKU/quantity from the array but, my goal is to get all listed in the columns with comma-separated.

JSON example:

{"shipments": [
    {
        "shipmentId": 100003768,
        "orderNumber": "9648219086",
        "shipDate": "2021-10-28",
        "serviceCode": "ups_ground",
        "shipmentItems": [
            {
                "orderItemId": 1464643208,
                "lineItemKey": "10322938560608",
                "sku": "SCPXTSS-BAG-06",
                "name": "SCOOP-PLATE-06 (1000ml)",
                "weight": {
                    "value": 0,
                    "units": "ounces",
                    "WeightUnits": 1
                },
                "quantity": 1,
                "unitPrice": 0,
                "taxAmount": null
                },
            {
                "orderItemId": 1464643207,
                "lineItemKey": "10322938527840",
                "sku": "SCPZRTS-TRAY-01",
                "name": "Beef: Tray 3 (Fill 004)<br>",
                "weight": {
                    "value": 60,
                    "units": "ounces",
                    "WeightUnits": 1
                },
                "quantity": 1,
                "unitPrice": 102.72,
                "taxAmount": null
                }
        ],
        "labelData": null,
        "formData": null
    }
 
]
}

SQL query I'm using:

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\Users\XPS-LT\json\today\shipments_20211031.json', SINGLE_CLOB) import
SELECT *
FROM OPENJSON (@JSON, '$.shipments') 
WITH 
(
    [shipmentId] bigint, 
    [orderNumber] nvarchar(60), 
    [shipDate] date, 
    [serviceCode] nvarchar(30), 
    [sku] nvarchar(MAX) N'$.shipmentItems[0].sku',
    [quantity] int N'$.shipmentItems[0].quantity' 
    )          
;

Current result

CodePudding user response：

The "shipmentItems" part of the input JSON is an array, so you need an AS JSON clause in the first explicit schema and an additional OPENJSON() call:

DECLARE @json nvarchar(max)
...

SELECT 
   j.[shipmentId], j.[orderNumber], j.[shipDate], j.[serviceCode],
   a.[sku], a.[quantity]
FROM OPENJSON (@json, '$.shipments') WITH (
   [shipmentId] bigint, 
   [orderNumber] nvarchar(60), 
   [shipDate] date, 
   [serviceCode] nvarchar(30), 
   [shipmentItems] nvarchar(max) AS JSON 
) j         
OUTER APPLY (
   SELECT 
      STRING_AGG([sku], ',') WITHIN GROUP (ORDER BY [orderItemId]),
      STRING_AGG([quantity], ',') WITHIN GROUP (ORDER BY [orderItemId])
   FROM OPENJSON (j.shipmentItems) WITH (
      [orderItemId] int '$.orderItemId',
      [sku] nvarchar(max) '$.sku',
      [quantity] int N'$.quantity' 
   )    
) a ([sku], [quantity])

Result:

shipmentId orderNumber shipDate   serviceCode sku                            quantity
100003768  9648219086  2021-10-28 ups_ground  SCPZRTS-TRAY-01,SCPXTSS-BAG-06 1,1