We’ll seen how to get JSON data from a file and how to insert JSON directly into a table.
But how do take data from our SQL Server queries and turn that data into valid JSON?
Turns out that we have a few options – and we’ll look at some right now.
And it’s actually quite simple, all we need to do is place FOR JSON AUTO on the very end of our T-SQL statement
USE [tempdb];
GO
SELECT
C.configuration_id
,[Configuration name] = C.[name]
,[Configuration Value] = C.[value]
,C.minimum
,C.maximum
,C.value_in_use
,C.[description]
,C.is_dynamic
,C.is_advanced
FROM
sys.configurations AS C
ORDER BY
C.configuration_id
FOR JSON AUTO;
Notice that we have aliased the columns “name” to “Configuration name” and “value” to “Configuration Value” – both of which can be seen from the JSON produced.
[
{
"configuration_id": 101,
"Configuration name": "recovery interval (min)",
"Configuration Value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"is_dynamic": true,
"is_advanced": true
},
{
"configuration_id": 102,
"Configuration name": "allow updates",
"Configuration Value": 0,
"minimum": 0,
"maximum": 1,
"value_in_use": 0,
"description": "Allow updates to system tables",
"is_dynamic": true,
"is_advanced": false
}
]
We can do something a something a touch more complex. Let’s see how many dynamic values we have on our instance
SELECT
[is_dynamic] =
CASE
WHEN C.is_dynamic = 0 THEN 'No'
ELSE 'YES'
END
,[Count] = COUNT(*)
FROM
sys.configurations AS C
GROUP BY
C.is_dynamic
ORDER BY
[is_dynamic]
FOR JSON AUTO;
Which will give us the JSON below
[
{
"is_dynamic": "No",
"Count": 17
},
{
"is_dynamic": "YES",
"Count": 66
}
]
And that’s all there really is too it.
We’ll have a look at something a bit flexible in the next instalment.
I hope this has helped on your SQL Server JSON journey. We’ll see more real soon.
Have a great day
Cheers
Marty
Download Files