Convert Json with array of arrays to columns in Sql server

Question

I have the json (example) below:

'{"X":["x1","x2","x3"],"Y":["y1","y2","y3"],"Z":[["x1y1","x1y2","x1y3"],["x2y1","x2y2","x2y3"],["x3y1","x3y2","x3y3"],["x4y1","x4y2","x4y3"],["x5y1","x5y2","x5y3"]]}'

(the true json has numbers, but for the example i am using strings) It represents values in tree dimentions (X,Y,Z)

I would like to convert that json into columns, like the table below:

X	Y	Z
x1	y1	x1y1
x1	y2	x1y2
x1	y3	x1y3
x2	y1	x2y1
x2	y2	x2y2
x2	y3	x2y1
..	..	....
..	..	....

How would be the SELECT statement ?

Z seems to be a nested array. Is this expected? The json contains a list of column values instead of rows too. Frankly, the easiest way would be to use a Python script with sp_execute_external_script, load this into a DataFrame, return it and write the output. JSON functions are not meant for reshaping data — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 9, 2022 at 12:30
@PanagiotisKanavos Yes, Z is a nested array, because for each X,Y combination there is a Z value. — Glayson Patricio
– Glayson Patricio, Commented Sep 9, 2022 at 12:34

Charlieface · Accepted Answer · 2022-09-09 12:59:04Z

It looks like you need to do a dynamic JSON_VALUE lookup based on the cross-join of X and Y properties.

Note that dynamic JSON paths only work in SQL Servre 2017 onwards, not 2016.

DECLARE @json nvarchar(max) = '{"X":["x1","x2","x3"],"Y":["y1","y2","y3"],"Z":[["x1y1","x1y2","x1y3"],["x2y1","x2y2","x2y3"],["x3y1","x3y2","x3y3"],["x4y1","x4y2","x4y3"],["x5y1","x5y2","x5y3"]]}';

SELECT
  x = x.value,
  y = y.value,
  z = JSON_VALUE(@json, '$.Z[' + x.[key] + '][' + y.[key] + ']')
FROM OPENJSON(@json, '$.X') x
CROSS JOIN OPENJSON(@json, '$.Y') y;

For SQL Server 2016, you instead just need to cross-join everything and filter afterwards

DECLARE @json nvarchar(max) = '{"X":["x1","x2","x3"],"Y":["y1","y2","y3"],"Z":[["x1y1","x1y2","x1y3"],["x2y1","x2y2","x2y3"],["x3y1","x3y2","x3y3"],["x4y1","x4y2","x4y3"],["x5y1","x5y2","x5y3"]]}';

SELECT
  x = x.value,
  y = y.value,
  z = z2.value
FROM OPENJSON(@json, '$.X') x
CROSS JOIN OPENJSON(@json, '$.Y') y
JOIN OPENJSON(@json, '$.Z') z1 ON z1.[key] = x.[key]
CROSS APPLY OPENJSON(z1.value) z2
WHERE z2.[key] = y.[key];

db<>fiddle

Collectives™ on Stack Overflow

Convert Json with array of arrays to columns in Sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related