How to import nested JSON to SQL Server table?

Question

I have a nested JSON variable(I use shortened version here) which I wanna insert into table. I am able to take not nested columns and values however struggling with nested part. What I want is retrieving 2 rows: one for sessionID = 20 and other one for 30 with sub columns. I am using SQL Server 2017.

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'{
"List":
[
    {
    "ID": 13,
    "Date": "2015-12-07",
    "SessionID": {
      "20": {
        "discount": "no",
        "price": 15.99
            },
      "30": {
       "discount": "yes",
        "price": 12.99
            }
        }
    }
]
}'  



SELECT *  
FROM OPENJSON(@json, N'lax $.List')
WITH (ID int '$.ID' 
     ,[Date] date '$.Date'
     ,SessionID nvarchar(max) N'lax $.SessionID' AS JSON
     )

I would like retrieve data as below. Thanks in advance for any help!

One option is using cross apply. e.g. adding cross apply openjson(sessionid) as s cross apply openjson(s.value) with (discount nvarchar(3) '$.discount', price money '$.price') as v (where sessionid would be s.key) — ZLK
– ZLK, Commented Nov 28, 2018 at 23:31
I hope there will be a day when questions containing proper sample data, shows effort and expected results will be so common they will not deserve an upvote - but it is not this day. — Zohar Peled
– Zohar Peled, Commented Nov 29, 2018 at 6:48
The problem here is that your json is ill formatted - a proper json should contain pairs of key:value, that might be nested (meaning that the value will also contain json data). However, in your case - you have the session ids (20 and 30) as keys instead of as values - making it much harder to extract them from the json using the standard openjson function. If you can change the source json to include the 20 and 30 as values, it would make your life much easier. — Zohar Peled
– Zohar Peled, Commented Nov 29, 2018 at 6:53

Gottfried Lesigang · Accepted Answer · 2018-11-29 11:59:54Z

As Zohard Peled told you, the internal structure of your JSON is something to improve. In general it is not a good idea to place data as element name. It was better to use something like "SessionID":"20", while your JSON comes up with the "20" as name of the following object.

Nevertheless this can be done, using the key column of OPENJSON:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'{
"List":
[
    {
    "ID": 13,
    "Date": "2015-12-07",
    "SessionID": {
      "20": {
        "discount": "no",
        "price": 15.99
            },
      "30": {
       "discount": "yes",
        "price": 12.99
            }
        }
    }
]
}'

--The query will travers down your structure step-by-step:

SELECT B.ID
      ,B.[Date]

      ,C.[key] AS SessionID --here's the magic...

      ,JSON_VALUE(C.[value],'$.discount') AS discount
      ,JSON_VALUE(C.[value],'$.price') AS price
FROM OPENJSON(@json)
WITH(List NVARCHAR(MAX) AS JSON) A --getting "List" as JSON
CROSS APPLY OPENJSON(A.List)
WITH(ID INT
    ,[Date] DATE
    ,SessionID NVARCHAR(MAX) AS JSON) B --getting "ID" and "Date" and "SessionID as JSON
CROSS APPLY OPENJSON(B.SessionID) C; --get the objects within the array, but nameless...

The result:

ID  Date    SessionID   discount    price
13  2015-12-07  20      no          15.99
13  2015-12-07  30      yes         12.99

Collectives™ on Stack Overflow

How to import nested JSON to SQL Server table?

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