Converting Json field & relational fields into multiple rows

Question

I am really new to Json & SQL Server. Since SQL Server 2016 can support Json values, i have a task in hand.

I have a SQL table with a CustomeID (int) Field & another field that holds a json array value which holds different elements that belongs to that unique customer.

Ex:

Customer ID | JsonCol 
55          | [{"Id":"12","Height":"150","Weight":"75","Colour":"White"}, 
              {"Id":"15","Height":"160","Weight":"85","Colour":"Brown"}]
65          | [{"Id":"16","Height":"155","Weight":"65","Colour":"Red"}, 
              {"Id":"20","Height":"167","Weight":"55","Colour":"Black"}]

I would like to do a query in SQL Server to get the following outcome, I dont know how to insert tables into this. But basically the output should repeat the Customer for every combination of data that the array has in the Json field

CustomerID   | ID | Height | Weight | Colour
55           | 12 | 150    | 75     | White
55           | 15 | 160    | 85     | Brown
65           | 16 | 155    | 65     | Red
65           | 20 | 167    | 55     | Black

Can someone so kindly give me an idea where to start. I have already tried all Microsoft articles around Json support.

Thank you in advance.

sticky bit · Accepted Answer · 2018-05-31 01:23:36Z

4

Use OPENJSON with the default output to get the array elements and then OPENJSON output with an explicit structure to get the elements. Put it together with CROSS APPLYs.

SELECT CustomerID,
       x.Id,
       x.Height,
       x.Weight,
       x.Colour
       FROM Customer C
       CROSS APPLY (SELECT *
                           FROM OPENJSON(C.JsonCol)
                           CROSS APPLY OPENJSON(value)
                                 WITH (Id integer '$.Id',
                                       Height integer '$.Height',
                                       Weight integer '$.Weight',
                                       Colour nvarchar(8) '$.Colour')) x;

SQL Fiddle

answered May 31, 2018 at 1:23

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

1 Comment

SuperWowuser Over a year ago

Worked like a charm. The more i read it - the more i understood what it was doing. Thank you so much. Really appreciated!

Collectives™ on Stack Overflow

Converting Json field & relational fields into multiple 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