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

hkravitz · Accepted Answer · 2018-03-22 09:20:32Z

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

edited Mar 22, 2018 at 9:20

answered Mar 22, 2018 at 8:18

hkravitz

1,4051 gold badge12 silver badges21 bronze badges

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

3 Comments

DataRiver Over a year ago

Thank you @hkravitz. This is perfect. Would it be possible to share some details on what that ToPvt and Final (Cmd) is doing? Also, if I did not really need to consume the column names and just insert the "data", what will be the best method?

hkravitz Over a year ago

@DataRiver, the ToPvt part is preparing the result set for the dynamic pivot, note that OPENJSON returns a key value reuslt set, and you need to pivot it in order to insert the data into a table with n columns. If you'll use a simple pivot you'll get 1 row for each combination of col1 to col4. The trick here is to create a pivot statement per each array of data you have in your JSON. if you want to extract all the values of the "data" element just use select * from [rows] T cross apply OPENJSON(T.[value]) x and you'll get all values listed as key value output. Let me know if you need more help

DataRiver Over a year ago

Thank you @hkravitz so much for the explanation. I will be tweaking around this to see how it fits my application.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related