SQL Server and JSON – JSON PATH

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.

Leave a Reply

Your email address will not be published. Required fields are marked *