We’ve looked at getting pulling data from a JSON document into relational table format using an explicit schema that was defined in the WITH clause of the OPENJSON table valued fumction.
However, in that example, we used a PATH expression that was taken from the root of the JSON document.
A question that I recently had was ‘What of you want to take the PATH from a certain point in the JSON document’ – and that’s what we will look at here.
We use this simple document, which contains one object
{
"Configuration Property": {
"Configuration name": "recovery interval (min)",
"Value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"is_dynamic": true,
"is_advanced": true
}
}
Here, we want to start retrieving data from the document starting at the path
$." "Configuration Property"
All we need to do is define the base PATH (the starting point) in our OPENJSON query.
DECLARE @json NVARCHAR(MAX) =
N'
{
"Configuration Property": {
"Configuration name": "recovery interval (min)",
"Value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"is_dynamic": true,
"is_advanced": true
}
}
';
SELECT
[Configuration name]
,[Value]
,[minimum]
,[maximum]
,[value_in_use]
,[description]
,[is_dynamic]
,[is_advanced]
FROM
OPENJSON(@json, '$."Configuration Property"')
WITH
(
[Configuration name] NVARCHAR(35)
,[Value] NVARCHAR(100)
,[minimum] NVARCHAR(100)
,[maximum] NVARCHAR(100)
,[value_in_use] NVARCHAR(100)
,[description] NVARCHAR(100)
,[is_dynamic] BIT
,[is_advanced] BIT
);
I hope this has helped on your SQL Server JSON journey. We’ll see more real soon.
Have a great day
Cheers
Marty