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

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.

CodePudding user response：

Create a .json file with the list you want to display , Name it as Results.json and save.
Remove the \ from starting and ending of the json object and save.

[
{
"orderItemId":123, 
"webproductID": 12345
},
{
"orderItemId": 234,
 "webproductID": 23456
}
]

Execute the below query

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\Harshitha\Results.JSON', SINGLE_CLOB) import
SELECT  1 AS Id,*
FROM OPENJSON (@JSON)
WITH 
(
    orderItemId INT, 
    webproductID INT  
)

Output

Update 1

As per your requirement , the JSON object table data is as follows

Got the desired results with the below query

select ID,  orderItemId, webproductID
from JsonTable
cross apply openjson(items) 
with (orderItemId INT, webproductID INT)

Update 2

Use the below query to fetch the dynamic properties from a json object.

CREATE TABLE #FinalTable(ID INT, ITEMS nvarchar(max))
insert into #FinalTable
select id,   json.value
from jsontable
     cross apply openjson(ITEMS) as json
DECLARE @stm nvarchar(max) = N''

-- Dynamic explicit schema (WITH clause)
SELECT  @stm = CONCAT(
   @stm,
   N', [',
   [key],
   N'] nvarchar(max) ''lax $."',
   [key],
   '"'''
)
FROM (
   SELECT DISTINCT  j.[key] FROM #FinalTable t
   CROSS APPLY OPENJSON(T.ITEMS) AS j
) cte

-- Statement   
SELECT @stm = CONCAT(
   N'SELECT ID,j.* ', 
   N'FROM #FinalTable t ', 
   N'CROSS APPLY OPENJSON(t.ITEMS) WITH (',
   STUFF(@stm, 1, 2, N''),
   N') j '
)

-- Execution
PRINT @stm
EXEC sp_executesql @stm
drop table #FinalTable