SQL OPENJSON read nested JSON with empty/null array

Question

I have simple JSON with nested arrays which some of them might be empty or null and I can't get whole that row in result because of null value.

declare @json nvarchar(max)

set @json = '{"orders": [{
                "id": 1,
                "items": 
                [{
                    "sku": "abc",
                    "quantity": 1,
                    "price": 100,
                    "discount": 
                        [{
                            "amount": "10",
                            "amount_set": 
                                {
                                "shop": 
                                    {
                                    "total": "10",
                                    "currency_code": "EUR"
                                    },
                                "presentment": 
                                    {
                                    "total": "10",
                                    "currency_code": "EUR"
                                    }
                                }
                        }]
                }]
                },{
                "id": 2,
                "items": 
                [{
                    "sku": "def",
                    "quantity": 1,
                    "price": 111,
                    "discount": []
                }]
                }
        ] }'

SELECT cOrderID, cSKU, nQty, nPrice, nDiscount
FROM 
(
    OPENJSON(@json, '$.orders') 
    WITH (
        cOrderID NVARCHAR(20) '$.id',  
        Items NVARCHAR(MAX) '$.items' AS JSON
        )
    CROSS APPLY OPENJSON(Items,'$')
    WITH (
        cSKU NVARCHAR(30) '$.sku',  
        nQty DECIMAL(19,6) '$.quantity',
        nPrice FLOAT '$.price',
        Discount NVARCHAR(MAX) '$.discount' AS JSON
        )
    CROSS APPLY OPENJSON(Discount,'$')
    WITH (
        nDiscount DECIMAL(19,6) '$.amount'
        )
)

I get this result:

cOrderID     cSKU          nQty          nPrice          nDiscount
------------ ------------- ------------- --------------- -----------------
1            abc           1.000000      100             10.000000

I expect to get this result:

cOrderID     cSKU          nQty          nPrice          nDiscount
------------ ------------- ------------- --------------- -----------------
1            abc           1.000000      100             10.000000
2            def           1.000000      111             null

How can I show rows with empty arrays?

hoggar · Accepted Answer · 2020-04-30 01:38:34Z

5

Solution is OUTER APPLY on Discount array:

SELECT cOrderID, cSKU, nQty, nPrice, nDiscount
FROM 
(
OPENJSON(@json, '$.orders') 
WITH (
    cOrderID NVARCHAR(20) '$.id',  
    Items NVARCHAR(MAX) '$.items' AS JSON
    )
CROSS APPLY OPENJSON(Items,'$')
WITH (
    cSKU NVARCHAR(30) '$.sku',  
    nQty DECIMAL(19,6) '$.quantity',
    nPrice FLOAT '$.price',
    Discount NVARCHAR(MAX) '$.discount' AS JSON
    )
OUTER APPLY OPENJSON(Discount,'$')
WITH (
    nDiscount DECIMAL(19,6) '$.amount'
    )
)

Result is exactly as expected:

cOrderID     cSKU          nQty          nPrice          nDiscount
------------ ------------- ------------- --------------- -----------------
1            abc           1.000000      100             10.000000
2            def           1.000000      111             null

answered Apr 30, 2020 at 1:38

hoggar

3,9076 gold badges34 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

SQL OPENJSON read nested JSON with empty/null array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related