[FIXED] How to parse JSON list of list using SQL Server

Issue

To parse data in a SQL Server database from an API that returns the data in the following format:

declare @json nvarchar(4000) = N'{
   "List":{
      "header":{
         "id":"id1",
         "resolution":"Hourly"
      },
      "values":[
         [
            {
               "dateTime":"2020-10-01T00:00:00",
               "unit":"MWh",
               "val":0.9
            },
            {
               "dateTime":"2020-10-01T01:00:00",
               "unit":"MWh",
               "val":1.1
            }
         ],
         [
            {
               "dateTime":"2020-10-02T00:00:00",
               "unit":"MWh",
               "val":0.5
            },
            {
               "dateTime":"2020-10-02T01:00:00",
               "unit":"MWh",
               "val":0.3
            }
         ]
      ]
   }
}'

The problem is that inside the key values is a list of lists in the json. I want the data in the following format:

id	dateTime	unit	val
id1	2020-10-01T00:00:00	MWh	0.9
id1	2020-10-01T01:00:00	MWh	1.1
id1	2020-10-02T00:00:00	MWh	0.5
id1	2020-10-02T01:00:00	MWh	0.3

I've tested with this code:

SELECT 
    (SELECT *
     FROM OPENJSON (@json, '$.List.header') 
          WITH (entityid varchar(200) '$.id')) id,
    *
FROM   
    OPENJSON (@json, '$.List.values') 
        WITH (datetime datetime '$.dateTime', 
              unit varchar(10) '$.unit', 
              val float '$.val')

but I get

id	dateTime	unit	val
id1	null	null	null
id1	null	null	null
id1	null	null	null
id1	null	null	null

Solution

It's because in values you have arrays in arrays. One method would be the below:

SELECT CONVERT(varchar(50),JSON_VALUE(l.header,'$.id')) AS id, vv.*
FROM OPENJSON(@json,'$.List')
     WITH (header nvarchar(MAX) AS JSON,
           [values] nvarchar(MAX) AS JSON) l
     CROSS APPLY OPENJSON(l.[values]) v
     CROSS APPLY OPENJSON(v.value)
                 WITH ([dateTime] datetime2(0),
                       unit varchar(20),
                       val decimal(5,1)) vv;

Answered By - Larnu

Answer Checked By - Cary Denson (PHPFixing Admin)