arraysjsonsql-serverlistopen-json

How to parse JSON list of list using SQL Server


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;