Consume JSON via OPENJSON and save in table - SQL Server 2016

Question

We recently upgraded our backend DB from SQL Server 2012 to SQL Server 2016 which I realized supports JSON objects. One of our web services return data in the below format, and I am trying to consume it directly using OPENJSON function.

{
"RESULT_1": {
    "columns": ["col1", "col2", "col3", "col4"],
    "data": [
        ["0", null, "12345", "other"],
        ["1", "a", "54321", "MA"],
        ["0", null, "76543", "RI"]
    ]
   }
}

I want to make sure that I read the column names from the "columns section" and make sure that the correct data is read and pushed in SQL Server 2016.

DECLARE @Json_Array2 nvarchar(max) = '{
"RESULT_1": {
    "columns": ["col1", "col2", "col3", "col4"],
    "data": [
        ["0", null, "12345", "other"],
        ["1", "a", "54321", "MA"],
        ["0", null, "76543", "RI"]
    ]
} }';

SELECT [key], value
FROM OPENJSON(@Json_Array2,'$.RESULT_1.columns')

SELECT [key], value
FROM OPENJSON(@Json_Array2,'$.RESULT_1.data')

With above statements, I am able to extract the columns and the data array. Would it be possible to dump this data in a flat table (already existing) with the same column names?

I am able to see all the values of one particular row by:

SELECT [key], value
FROM OPENJSON(@Json_Array2,'$.RESULT_1.data[0]')


key value
0   0
1   NULL
2   12345
3   other

Any pointers are appreciated.

Thank you

Answer 1

If I understand correctly you are trying to extract all elements from the "data" array

Col1    Col2    Col3    Col4
0      NULL    12345    other
1      a       54321    MA
0      NULL    76543    RI

Suppose your Json Structure will not change you can achieve that using the following query:

SET NOCOUNT ON 
IF OBJECT_ID ('tempdb..##ToPvt') IS NOT NULL DROP TABLE ##ToPvt
DECLARE @Cmd NVARCHAR(MAX)=''
DECLARE @Table TABLE (Col1 nvarchar(100), Col2 nvarchar(100), Col3 nvarchar(100) , Col4 nvarchar(100))
DECLARE @Json_Array2 nvarchar(max) = '{
"RESULT_1": {
    "columns": ["col1", "col2", "col3", "col4"],
    "data": [
        ["0", null, "12345", "other"],
        ["1", "a", "54321", "MA"],
        ["0", null, "76543", "RI"]
    ]
} }';

;WITH Cols ([Key], [Value]) as
(
SELECT [key], value
FROM OPENJSON(@Json_Array2,'$.RESULT_1.columns')
)

, [Rows] as
(
SELECT ROW_NUMBER () OVER (ORDER BY [Key]) Seq, [key], value
FROM OPENJSON(@Json_Array2,'$.RESULT_1.data')
) 
,ToPvt as 
(
SELECT c.[Key]+1 Cols, x.Value , 'col'+CONVERT(VARCHAR(10),ROW_NUMBER () OVER (PARTITION BY c.Value ORDER BY t.[Key])) Seq
 FROM [Rows] t
 INNER JOIN Cols C
 ON t.[key] = c.[key]
 CROSS APPLY OPENJSON(t.value) X
 )

 SELECT * 
 INTO ##ToPvt
 FROM ToPvt 

 ;WITH Final (Cmd) as
 (
 SELECT DISTINCT 'SELECT [col1], [col2], [col3],[col4] FROM ##ToPvt 
            PIVOT
                (
                MAX([Value]) FOR Seq IN ([col1], [col2], [col3],[col4])
                ) T
             WHERE Cols = '+CONVERT(VARCHAR(10),Cols)+'
                ;'
 FROM ##ToPvt
 )

 SELECT @Cmd += Cmd
 FROM Final 

 INSERT INTO @Table 
 EXEC sp_executesql @Cmd

 SELECT * 
 FROM @Table