I am trying to flatten nested array from JSON in SQL Server, using the code below, but without success.

Used JSON

'{
    "shipmentDetails": {
        "shipmentId": "JHVJD5627278788"
    },
    "shipmentStops": [
        {
            "stopSequence": 1,
            "orderReferenceNumbers": [
                "2120549020", "test"
            ]
        },
        {
            "stopSequence": 2,
            "orderReferenceNumbers": [
                "2120549020", "2120549002"
            ]
        }
    ]
}'

DECLARE @Step AS NVARCHAR(max) = N'Variables declaration';
DECLARE @JSON1 AS NVARCHAR(MAX);

SET @JSON1 = '{
    "shipmentDetails": {
        "shipmentId": "JHVJD5627278788"
    },
    "shipmentStops": [
        {
            "stopSequence": 1,
            "orderReferenceNumbers": [
                "2120549020", "test"
            ]
        },
        {
            "stopSequence": 2,
            "orderReferenceNumbers": [
                "2120549020", "2120549002"
            ]
        }
    ]
}'

IF OBJECT_ID('JSONPO2') IS NOT NULL
    DROP TABLE JSONPO2

SET @Step = N'JSON data parsing and loading into JSONPO2 temp table'

SELECT DISTINCT ShipDetails.shipmentId AS shipmentId
    ,ShipmentStops.stopSequence AS stopSequence
    ,ShipmentStops.orderReferenceNumbers AS orderReferenceNumbers
INTO JSONPO2
FROM OPENJSON(@JSON1) WITH (
        shipmentDetails NVARCHAR(MAX) AS JSON
        ,shipmentStops NVARCHAR(MAX) AS JSON
        ) AS [Data]
CROSS APPLY OPENJSON(shipmentDetails) WITH (shipmentId NVARCHAR(20)) AS ShipDetails
CROSS APPLY OPENJSON(shipmentStops) WITH (
        stopSequence INT
        ,orderReferenceNumbers NVARCHAR(MAX) AS JSON
        ) AS ShipmentStops
CROSS APPLY OPENJSON(orderReferenceNumbers) WITH (orderReferenceNumbers VARCHAR(max)) 
AS orderReferenceNumbers

SELECT *
FROM JSONPO2

From the above code, I receive only two rows with a strange array

How to change the code to parse nested arrays and have four rows like below?

Appreciate any help :)

CodePudding user response：

Your issue is when trying to expand the array using the WITH clause:

CROSS APPLY OPENJSON(orderReferenceNumbers) 
   WITH (orderReferenceNumbers VARCHAR(max)) AS orderReferenceNumbers

The JSON you are opening though has no property "orderReferenceNumbers", it is simply:

["2120549020", "test"]

So this is returning NULL. You'd see this in your select if you were selecting orderReferenceNumbers.orderReferenceNumbers rather than ShipmentStops.orderReferenceNumbers.

You don't need to use WITH if you are opening a simple JSON array of primitive types. The following query will return the output you are after:

DECLARE @JSON1 AS NVARCHAR(MAX) = N'{
    "shipmentDetails": {
        "shipmentId": "JHVJD5627278788"
    },
    "shipmentStops": [
        {
            "stopSequence": 1,
            "orderReferenceNumbers": [
                "2120549020", "test"
            ]
        },
        {
            "stopSequence": 2,
            "orderReferenceNumbers": [
                "2120549020", "2120549002"
            ]
        }
    ]
}';

SELECT  sd.shipmentId, 
        ss.stopSequence, 
        orderReferenceNumber = orn.Value
FROM    OPENJSON(@JSON1)
            WITH(shipmentDetails NVARCHAR(MAX) AS JSON, shipmentStops NVARCHAR(MAX) AS JSON) AS d
        CROSS APPLY OPENJSON(d.shipmentDetails)
            WITH(shipmentId NVARCHAR(20)) AS sd
        CROSS APPLY OPENJSON(d.shipmentStops)
            WITH(stopSequence INT, orderReferenceNumbers NVARCHAR(MAX) AS JSON) AS ss
        CROSS APPLY OPENJSON(orderReferenceNumbers) AS orn;

Example on db<>fiddle

As an aside, if you will only ever have one shipmentId in the JSON (which would make sense otherwise you'd end up with cross joins) you can simplify this slightly, and remove one of the OPENJSON()s:

SELECT  d.shipmentId, 
        ss.stopSequence, 
        orderReferenceNumber = orn.Value
FROM    OPENJSON(@JSON1)
            WITH(ShipmentId NVARCHAR(20) '$.shipmentDetails.shipmentId', 
                shipmentStops NVARCHAR(MAX) AS JSON) AS d
        CROSS APPLY OPENJSON(d.shipmentStops)
            WITH(stopSequence INT, orderReferenceNumbers NVARCHAR(MAX) AS JSON) AS ss
        CROSS APPLY OPENJSON(ss.orderReferenceNumbers) AS orn;