I am trying to extract data from the following JSON and pivot the data in "rows" to columns.
{
"tables": [
{
"name": "PrimaryResult",
"columns": [
{
"name": "timestamp",
"type": "datetime"
},
{
"name": "message",
"type": "string"
},
{
"name": "severityLevel",
"type": "int"
},
{
"name": "FriendlySeverityLevel",
"type": "string"
},
{
"name": "CycleId",
"type": "dynamic"
}
],
"rows": [
[
"2024-07-25T12:00:00",
"Report Started - User: [email protected] MrmClient: 400600 CcID: 000a000b-0000-111c-0a0b-11111a1111a1",
1,
"Information",
"000a000b-0000-111c-0a0b-11111a1111a1"
],
[
"2024-07-24T12:00:00",
"Report Started - User: [email protected] MrmClient: 807231 CcID: 000c000d-0000-11c1-0c0d-11111b11111b",
1,
"Information",
"000c000d-0000-11c1-0c0d-11111b11111b"
]
]
}
]
}
This is what I'm trying to get for the final result:
Using JSON_VALUE(RawJSON, '$.tables[0].name')
gives me PrimaryResult but I can't figure out how to pull the data from "rows". I've tried JSON_VALUE(RawJSON, '$.tables[0].rows[0]')
, but it returns a NULL. Any help would be greatly appreciated.
Best Answer
If the provided JSON content contains information about more than one table, a possible approach is a dynamic statement. The idea is to generate a statement which uses
OPENJSON()
with the appropriate columns definitions for each table using the information from the$.tables[*].columns
parts in the input JSON.Test JSON with information for two tables:
T-SQL:
The generated statement, based on the test data, is posted below. All columns are defined as
nvarchar(max)
columns, but you can easily include the appropriateCASE
expression and map the columns data types (datetime
,string
,int
anddynamic
in the example) to the appropriate SQL Server data types.