How to extract multi-nested values from JSONs in T-SQL

Question

I am using SSMS v18.12.1, and am trying to write a T-SQL query to extract specific values from multiple columns, and at times nested values.

In the example below, I would like to extract the DeliverySchedule @Type value, and the Details @Type and Details:candyLimit @Type value

{
    "@type": "Test",
    "OrderId": 6608216,
    "deliverySchedule": {
        "@DeliveryType": "Test",
        "details": [
            {
                "@type": "Snickers",
                "id": 657117,
                "candyLimit": {
                    "@type": "UNRESTRICTED"
                }
            }
        ]
    }
}

I am still pretty new to extracting JSON data. I have tried utilizing suggestions from other StackOverflow questions, have tried using an OPENJSON similar to this, but it is not returning the results I expected

select * from openjson((select [description] from #OrderHistory)
,'$.deliverySchedule.details[1].candyLimit[1].@type'
)

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

select * from #OrderHistory OH
CROSS APPLY OPENJSON(OH.description)

Returns Key/Value pairs in separate columns, and the Value column still has nested JSON

select * from openjson((select [description] from #OrderHistory)
,'$.deliverySchedule.details[1].candyLimit[1].@type'
)

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

select * from #OrderHistory OH
CROSS APPLY OPENJSON(OH.description)

Returns Key/Value pairs in separate columns, and the Value column still has nested JSON

Fyi the version of ssms is irrelevant, it's only a client ui, we need the version of the engine i.e. `select @@version` — Dale K, Feb 10 '23 at 23:21

Martin Smith · Answer 1 · 2023-02-11T17:30:48.263

0

One way is to chain together some OPENJSON calls with explicit schema (fiddle)

Arrays are zero based so you need [0] not [1] (if you are specifically interested in the first array element only as seems to be the case from the code you posted) and candyLimit is just an object so doesn't need any index at all.

SELECT      deliverySchedule."@type",
            details1."@type",
            candyLimitType
FROM        #OrderHistory OH
CROSS APPLY OPENJSON(OH.description) 
    WITH ( "@type" VARCHAR(20), 
            details1 NVARCHAR(MAX) '$.deliverySchedule.details[0]' AS JSON ) deliverySchedule
CROSS APPLY OPENJSON(details1) 
    WITH ( "@type"       VARCHAR(10), 
           candyLimitType NVARCHAR(MAX) '$.candyLimit."@type"') details1

edited Feb 11 '23 at 17:30

answered Feb 10 '23 at 21:09

Martin Smith

438,706
87
741
845

you can use ""-names in paths to escape special chars, like: $."some json field" – siggemannen Feb 11 '23 at 08:05
@siggemannen - thanks. I thought I'd tried that! – Martin Smith Feb 11 '23 at 17:31

score 0 · Answer 2 · answered Feb 11 '23 at 08:10

If you're only interested in flat values (ie. the first array value), you can use JSON_VALUE directly:

create table #json (j nvarchar(max))

insert into #json
SELECT N'
{
    "@type": "Test",
    "OrderId": 6608216,
    "deliverySchedule": {
        "@DeliveryType": "Test",
        "details": [
            {
                "@type": "Snickers",
                "id": 657117,
                "candyLimit": {
                    "@type": "UNRESTRICTED"
                }
            }
        ]
    }
}'

select json_value(j,'$."@type"'), json_value(j,'$.deliverySchedule."@DeliveryType"')
, json_value(j, '$.deliverySchedule.details[0]."@type"')
from #json

How to extract multi-nested values from JSONs in T-SQL

2 Answers2