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

score 2 · Accepted Answer · answered May 13 '21 at 11:38

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;

score 0 · Answer 2 · answered Jul 06 '22 at 08:50

Necromancing.
The solution by Lamu is generally a bit precarious, because you need a schema and it might change.

Why not recursively resolve an arbitrary json-object into a key-value collection ? Like this:

DECLARE @json NVARCHAR(MAX);
SET @json = N'{
  "id": 168,
  "booleic": true,
  "foo": null,
  "good": {
    "AB":"cd", 
    "ef": "GH"
    ,"test":{
        "IJ": "kl",
        "mn": "OP"
    }
  },
  "features": {"test":1, "production": 2},
  "a": "[1,2]",
  "b": [1, 2],
  "c": "hi",
  "d": [{ "hello":{"ahörnchen":"bhörnchen", "deep": [ "space", "nine" ] }},{ "hello2":"kitty2"} ], 
  "foo-foo": "the dog" 
}';
-- SET @json = JSON_MODIFY(@json, '$.info.address[1].town', 'London');

-- SET @json = 'bla'
IF 0 = ISJSON(@json)
BEGIN
    -- https://learn.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-ver16
    -- 11 or higher => is caught in try/catch 
    -- severity 19+ requires sysadmin privileges
    -- severity 20+ terminates connection 
    -- state: is used to indicate position, if multiple raiseerrors in statement 
    -- arguments: is used to printf format message
    -- RAISEERROR msg, severity, state, [agruments] <with option>
    RAISERROR ('@json is not a valid JSON value.', 18, 1);  
END 





;WITH CTE AS 
(
    SELECT
         x."key", x."value", x."type" 
        ,0 AS lvl 
        ,CAST(ROW_NUMBER() OVER(ORDER BY x."key", x."value", x."type") AS nvarchar(MAX)) AS rn 
        ,1 AS origin 
        ,'$.' + x."key" AS json_path 
    FROM  ( SELECT @json as js ) AS t 
    OUTER APPLY OPENJSON(JSON_QUERY(t.js, '$')) AS x

    
    UNION ALL 

    SELECT 
         y."key", y."value", y."type" 
        ,CTE.lvl + 1 AS lvl 
        ,CTE.rn + '.' + CAST(ROW_NUMBER() OVER(ORDER BY y."key", y."value", y."type") AS nvarchar(MAX)) AS rn 
        ,2 AS origin 
        ,CTE.json_path + '.' + y."key" AS json_path 
    FROM CTE 
    OUTER APPLY OPENJSON(JSON_QUERY(CTE.value, '$')) AS y
    WHERE CTE."type" = 5


    
    UNION ALL 

    SELECT 
         y."key", y."value", y."type" 
        ,CTE.lvl + 1 AS lvl 
        ,CTE.rn + '.' + CAST(ROW_NUMBER() OVER(ORDER BY y."key", y."value", y."type") AS nvarchar(MAX)) AS rn 
        ,3 AS origin 
        ,CTE.json_path + '[' + y.nonkey + ']' AS foo 
    FROM CTE 
    OUTER APPLY 
    (
        SELECT CTE.[key] AS "key", aaaaa."value" AS "value", aaaaa."type", aaaaa."key" as nonkey FROM OPENJSON(CTE."value") AS aaaaa
    ) AS y

    WHERE CTE."type" = 4
)

SELECT * 
    ,CASE type 
        WHEN 0 THEN 'NULL' 
        WHEN 1 THEN 'string' 
        WHEN 2 THEN 'number' 
        WHEN 3 THEN 'boolean' 
        WHEN 4 THEN 'array' 
        WHEN 5 THEN 'object' 
        ELSE 'INVALID' 
    END AS type_desc 
FROM CTE 
WHERE (1=1) 
AND type < 4 -- scalar, including NULL 
-- AND type > 3 -- object or array 

-- ORDER BY lvl, rn 
ORDER BY json_path 

-- Type: 
-- 0: null
-- 1: string
-- 2: number 
-- 3: boolean
-- 4: array
-- 5: object

For your json, that yields:

How to parse JSON list of list using SQL Server

2 Answers2