CODEWITHSUNDEEP
sqljsonsql-serversql-server-2019
CutePoison
CutePoison

Reputation: 5365

Unpack all arrays in a JSON column SQL Server 2019

Say I have a table Schema.table with these columns

id | json_col

on the forms e.g

id=1
json_col ={"names":["John","Peter"],"ages":["31","40"]}

The lengths of names and ages are always equal but might vary from id to id (size is at least 1 but no upper limit).

How do we get an "exploded" table - a table with a row for each "names", "ages" e.g

id | names | ages 
---+-------+------
 1 | John  | 31
 1 | Peter | 41
 2 | Jim   | 17
 3 | Foo   |  2
.
.

I have tried OPENJSON and CROSS APPLY but the following gives any combination of names and ages which is not correct, thus I need to to a lot of filtering afterwards

SELECT *
FROM Schema.table
CROSS APPLY OPENJSON(Schema.table,'$.names')
CROSS APPLY OPENJSON(Schema.table,'$.ages')

Upvotes: 0

Views: 2082

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Here's my suggestion

DECLARE @tbl TABLE(id INT,json_col NVARCHAR(MAX));
INSERT INTO @tbl VALUES(1,N'{"names":["John","Peter"],"ages":["31","40"]}')
                      ,(2,N'{"names":["Jim"],"ages":["17"]}');

SELECT t.id
      ,B.[key] As ValueIndex
      ,B.[value] AS PersonNam
      ,JSON_VALUE(A.ages,CONCAT('$[',B.[key],']')) AS PersonAge
FROM @tbl t
CROSS APPLY OPENJSON(t.json_col) 
            WITH(names NVARCHAR(MAX) AS JSON
                ,ages  NVARCHAR(MAX) AS JSON) A
CROSS APPLY OPENJSON(A.names) B;

The idea in short:

  • We use OPENJSON with a WITH clause to read names and ages into new json variables.
  • We use one more OPENJSON to "explode" the names-array
  • As the key is the value's position within the array, we can use JSON_VALUE() to read the corresponding age-value by its position.

One general remark: If this JSON is under your control, you should change this to an entity-centered approach (array of objects). Such a position dependant storage can be quite erronous... Try something like

{"persons":[{"name":"John","age":"31"},{"name":"Peter","age":"40"}]}

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

Conditional Aggregation along with applying CROSS APPLY might be used :

SELECT id, 
       MAX(CASE WHEN RowKey = 'names' THEN value END) AS names,
       MAX(CASE WHEN RowKey = 'ages' THEN value END) AS ages
  FROM
  (
   SELECT id, Q0.[value] AS RowArray, Q0.[key] AS RowKey
     FROM tab
    CROSS APPLY OPENJSON(JsonCol) AS Q0
  ) r
 CROSS APPLY OPENJSON(r.RowArray) v
 GROUP BY id, v.[key]   
 ORDER BY id, v.[key]

 id | names | ages 
 ---+-------+------
  1 | John  | 31
  1 | Peter | 41
  2 | Jim   | 17
  3 | Foo   |  2

Demo

The first argument for OPENJSON would be a JSON column value, but not a table itself

Upvotes: 1

Related Questions