How to flatten nested array from JSON in SQL Server

Question

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

shipmentId	stopSequence	orderReferenceNumbers
JHVJD5627278788	1	[ "2120549020", "test" ]
JHVJD5627278788	2	[ "2120549020", "2120549002" ]

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

shipmentId	stopSequence	orderReferenceNumbers
JHVJD5627278788	1	2120549020
JHVJD5627278788	1	test
JHVJD5627278788	2	2120549020
JHVJD5627278788	2	2120549002

Appreciate any help :)

GarethD · Accepted Answer · 2022-12-08 16:17:01Z

2

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;

edited Dec 8, 2022 at 16:17

answered Dec 8, 2022 at 16:10

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

adam_szmyt Over a year ago

Indeed, removing of WITH clause helped, but there is also necessary part in SELECT orderReferenceNumber = orn.Value Why is this needed?

GarethD Over a year ago

When you don't specify the columns when using OPENJSON() it will return 3 columns: key, value and type, and the value column contains the items in the array, so I am just including this in the select and giving it an alias of orderReferenceNumber to match your desired output. dbfiddle.uk/NS1qiCNK

Collectives™ on Stack Overflow

How to flatten nested array from JSON in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related