How to flatten and transpose a hybrid table in SQL Server 2016 with JSON arrays

Question

I have a table, like such:

| f1 [int] | f2 [int] | jo1 [json object] | ja [json array] |

ja is an array of multiple json objectd, structured like so:

[
 {
  "ja_f1": x [int],
  "ja_f2": y [float]
 },
 ...
]

For the sake of simplicity, let's assume the jo1 is also structured like the objects in ja

How do I flatten this to a single record? I'm trying to make it this:

| f1 | f2 | jo1_f1 | jo1_f2 | ja[0]ja_f1 | ja[0]ja_f2 | ... | ja[n]ja_f2 |

I've been using a CURSOR on OPENJSON with a CROSS APPLY to bring back the missing data, but it breaks it into segments and it's not really what I need. This is what it looks like:

DECLARE @CursorForJSON CURSOR;

DECLARE @JSON nvarchar(MAX); BEGIN SET @CursorForJSON = CURSOR FOR SELECT JSON_Query(ja) FROM mytable

OPEN @CursorForJSON
FETCH NEXT FROM @CursorForJSON
INTO @JSON

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT mt.f1 AS 'f1',
        ja_f1,
        ja_f2
    FROM OPENJSON( @json )
    WITH ( ja_f1 INT '$.ja_f1',
        ja_f2 FLOAT '$.ja_f2')
    CROSS APPLY mytable as mt
    GROUP BY mt.f1, ja_f1, ja_f2
    ORDER BY mt.f1 ASC;
    FETCH NEXT FROM @CursorForJSON
    INTO @JSON
END;

CLOSE @CursorForJSON ;
DEALLOCATE @CursorForJSON;
END;

Any guidance would be appreciated

EDIT: Fixed some errors in the pseudo code

Don't, don't, don't. Since you don't know the structure of your result set in advance, this is going to require dynamic SQL. Even worse, because the structure of a result set cannot change mid-stream, you'd need to walk all the arrays to find the maximum length of any given array before you can even begin returning rows. Strongly consider leaving parsing this stuff up to the client (if this logic has to be used repeatedly) or do stuff like serving up the array as comma-separated values to be separated by something like Excel later (if this is a one-off). — Jeroen Mostert

MosaicOrange MosaicOrange · Accepted Answer · 2017-10-30T17:27:25

I was approaching this problem from the wrong angle, this is what I was looking to do...

SELECT mt.f1 AS 'f1',
    mt.f2 AS 'f2',
    JSON_VALUE(mt.jo1_f1,'$.jo_f1') AS 'jo f1',
    JSON_VALUE(mt.jo1_f2,'$.jo_f2') AS 'jo f2',
    j.ja_f1,
    j.ja_f2
FROM mytable mt
CROSS APPLY OPENJSON( mt.ja ) WITH (
        ja_f1 INT '$.ja_f1',
        ja_f2 FLOAT '$.ja_f2'
    ) j
GROUP BY mt.f1, mt.jo1_f1, mt.jo1_f2, j.ja_f1, j.ja_f2
ORDER BY mt.f1 ASC;

How to flatten and transpose a hybrid table in SQL Server 2016 with JSON arrays

1 Answers