CODEWITHSUNDEEP
sqljsonsql-server
chrisSpaceman
chrisSpaceman

Reputation: 249

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

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!

Upvotes: 2

Views: 671

Answers (1)

Serkan Arslan
Serkan Arslan

Reputation: 13393

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

Upvotes: 6

Related Questions