Split a list of multiple dictionaries into the columns of a new table

Viewed 226

so I'm working on an Azure SQL server and I'm trying to find a way to split a list of dictionaries into multiple columns with an sql statement. For example the table looks similar to:

ID | ITEMS
1  | [{"orderItemId":123, "webproductID": 12345}, {"orderItemId": 234, "webproductID": 23456}, ...]
2  | [{"orderItemId":345, "webproductID": 34567}, {"orderItemId": 456, "webproductID": 45678}, ...]    
                        .
                        .
                        . 

And I want to create a table that looks like this:

ID | orderID | webproductID
1  |   123   |     12345
1  |   234   |     23456    
2  |   345   |     34567
2  |   456   |     45678
          .
          .
          .

Since this is an example it doesn't represent the real data, where the column ITEMS has for every row a list with over 10 arrays (not fixed) and each array has over 30 fields inside {orderID, webproductID, ...} (also not fixed), if someone has a solution as to how to convert it without typing every field inside ITEMS it would be much appreciated.

I tried already opening it with JSON_VALUE but the thing is you cant get more than one entry from the JSON.

Select ID, JSON_VALUE(ITEMS, '$[0].orderItemId') as orderItemID

Especially [0] is necessary, otherwise I don't get anything at all.

0 Answers
Related