How to parse JSON list of list using SQL Server

Question

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

Answer 1

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;