Question 109798 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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

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

Tags:

2

Answers


  1. 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
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search