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.