G’day,
Previously, we have looked at using OPENJSON to gain knowledge about the JSON document that we have presented to the function.
A bit like this
SELECT
*
FROM
OPENJSON
(
'
{
"configuration_id": 101,
"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
}
'
)

Notice that we didn’t request any columns in the SELECT statement, but we got three columns back
- Key
- value
- type
That’s great metadata information – but what if we wanted the actual values from the JSON.
Well, the statement above used OPENJSON with the default schema – which is basically no column list defined. If we want to define a list then we need to use a WITH clause that defines an EXPLICIT schema – like so
SELECT
*
FROM
OPENJSON
(
'
{
"configuration_id": 101,
"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
}
'
)
WITH
(
[configuration_id] INT
,[Configuration name] NVARCHAR(35)
,[Value] NVARCHAR(200)
,[minimum] NVARCHAR(200)
,[maximum] NVARCHAR(200)
,[value_in_use] NVARCHAR(200)
,[description] NVARCHAR(200)
,[is_dynamic] BIT
,[is_advanced] BIT
);
GO

You might also notice that the names in the WITH clause match those in the JSON document – We can also add these as a column list to the SELECT statement, rather than using SELECT *
Notice also that if we ask for a value in the WITH clause that does not appear in the JSON document (maybe because of a typo) then we simply get a NULL returned in the SELECT list.
Notice here that the name of the first column is incorrect. So we get a null in the resultset.
SELECT
*
FROM
OPENJSON
(
'
{
"configuration_id": 101,
"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
}
'
)
WITH
(
[configuration_if] INT
,[Configuration name] NVARCHAR(35)
,[Value] NVARCHAR(200)
,[minimum] NVARCHAR(200)
,[maximum] NVARCHAR(200)
,[value_in_use] NVARCHAR(200)
,[description] NVARCHAR(200)
,[is_dynamic] BIT
,[is_advanced] BIT
);
GO

This is perhaps one reason that we should include an explicit column list in the SELECT statement
SELECT
[configuration_id]
,[Configuration name]
,[Value]
,[minimum]
,[maximum]
,[value_in_use]
,[description]
,[is_dynamic]
,[is_advanced]
FROM
OPENJSON
(
'
{
"configuration_id": 101,
"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
}
'
)
WITH
(
[configuration_id] INT
,[Configuration name] NVARCHAR(35)
,[Value] NVARCHAR(200)
,[minimum] NVARCHAR(200)
,[maximum] NVARCHAR(200)
,[value_in_use] NVARCHAR(200)
,[description] NVARCHAR(200)
,[is_dynamic] BIT
,[is_advanced] BIT
);
GO
And we pretty much see exactly what we had before.

Next up is some more useful tips about OPENJSON
Have a great day
Cheers
Marty
Download Files