Convert Json with array of arrays to columns in Sql server-CodePudding

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 ?

CodePudding user response：

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