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