So, we’ve seen how FOR JSON AUTO works. It’s fairly simple – just add the clause onto the end of the SQL statement
But what happens if we need more control over how the JSON will look?
Well, we have another clause that’ll do that for us.
Similar to FOR JSON AUTO – this one is called FOR JSON PATH
For Example using out FOR JSON AUTO clause we ended up with JSON that looked like this. (I’ve just selected the TOP 1 row of the sys.configurations table here)
[
{
"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
}
]
But what if we wanted our JSON to look something like this
[
{
"configuration_id": 101,
"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
}
}
]
Well, we use FOR JSON PATH – and we also alias the T-SQL code. You’ll notice that there is a new block called “Configuration_Property” that contains 8 of the properties.
so, here’s the query that produced the above output
SELECT
[configuration_id] = C.configuration_id
,[Configuration_Property.Configuration name] = C.[name]
,[Configuration_Property.Value] = C.[value]
,[Configuration_Property.minimum] = C.minimum
,[Configuration_Property.maximum] = C.maximum
,[Configuration_Property.value_in_use] = C.value_in_use
,[Configuration_Property.description] = C.[description]
,[Configuration_Property.is_dynamic] = C.is_dynamic
,[Configuration_Property.is_advanced] = C.is_advanced
FROM
sys.configurations AS C
ORDER BY
C.configuration_id
FOR JSON PATH;
I think that although the JSON represents the same information, it’s easier to read. I also think it can make queries against the JSON easier to understand, but we’ll have a look at that in a later instalment.
I hope this helps
Have a great day.
Cheers
Marty.