Select one row per JSON element in SQL field / convert JSON field into rows

Question

I have a table with lots of rows like:

ID     |     Attributes
1      |     {"Rank":1, "LoadLocation": London, "Driver":Tom}
2      |     {"Rank":2, "LoadLocation": Southampton, "Driver":Dick}
3      |     {"Rank":3, "DischargeLocation": Stratford}

There isn't a template for the JSON - it's a dumping ground for any number of attributes of the ID rows.

For use in a join I'd like to get these into a table this:

ID     |     Attribute Name     |  Attribute Value 
1      |     'Rank'             |   1
1      |     'LoadLocation'     |   'London'
1      |     'Driver'           |   'Tom'
2      |     'Rank'             |    2
2      |     'LoadLocation'     |   'Southampton'
2      |     'Driver'           |   'Dick'
3      |     'Rank'             |    3 
3      |     'DischargeLocation'|   'Stratford'

I can see that I probably need to be using OpenJSON, but also that for that I likely need to know the explicit structure. I don't know the structure, even to the point of each row having a different numbe of attributes.

Any help gratefully received!

I assume you're using SQL Server 2016+? What have you trued so far? — Thom A
– Thom A ♦, Commented Oct 7, 2019 at 14:07
It's a pretty straight forward parsing, just want to make sure I can test it — Kelevra
– Kelevra, Commented Oct 7, 2019 at 14:09
@Lamu - yes, I figure it'd be easier to cope with the denormalised table in a join than a large table whose column names I wouldn't know (as anything could be in the JSON string) — chrisSpaceman
– chrisSpaceman, Commented Oct 7, 2019 at 14:16

Serkan Arslan · Accepted Answer · 2019-10-07 14:17:07Z

6

If you have sql-server-2016 and above, you can use OPENJSON with CROSS APPLY

DECLARE @TestData TABLE (ID INT, Attributes VARCHAR(500))
INSERT INTO @TestData VALUES
(1 ,'{"Rank":1, "LoadLocation": "London", "Driver":"Tom"}'),
(2 ,'{"Rank":2, "LoadLocation": "Southampton", "Driver":"Dick"}'),
(3 ,'{"Rank":3, "DischargeLocation": "Stratford"}')


SELECT T.ID, X.[key] AS [Attribute Name], X.value AS [Attribute Value] 
FROM @TestData T
    CROSS APPLY (SELECT * FROM OPENJSON(T.Attributes)) AS X

Result:

ID          Attribute Name       Attribute Value    
----------- -------------------- -------------------
1           Rank                 1
1           LoadLocation         London
1           Driver               Tom
2           Rank                 2
2           LoadLocation         Southampton
2           Driver               Dick
3           Rank                 3
3           DischargeLocation    Stratford

answered Oct 7, 2019 at 14:17

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chrisSpaceman Over a year ago

Amazing I can definitely move on with this - thanks very much

Collectives™ on Stack Overflow

Select one row per JSON element in SQL field / convert JSON field into rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related