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!

Answer 1

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