JSON_QUERY FUNCTION IN SQL SERVER 2016

By Samir Behara on September 25, 2017 • ( 1 )

SQL Server 2016 provides built-in support for storing, managing and parsing JSON data. In my previous blog posts, we looked at ISJSON and JSON_VALUE function in details.

In this article, we will look at another new function in SQL Server 2016 – JSON_QUERY which you can use to extract an object or array from a JSON string.

JSON_QUERY Function

This might look pretty similar to JSON_VALUE function. However it has a distinct difference in its use case.

JSON_VALUE function returns a scalar value whereas JSON_QUERY returns an object or an array from the JSON data.

Let us now see a demonstration of the JSON_QUERY function in details —

	— JSON_QUERY Demonstration to extract an object or array from the JSON data
	DECLARE @json nvarchar(max)
	SET @json =N'
	{
	"Person": [
	{
	"Name": {
	"FirstName": "Catherine",
	"LastName": "Abel"
	},
	"Address": {
	"State": "VA",
	"Zip": "24153"
	}
	}
	]
	}'


	IF (ISJSON(@json) = 1)
	PRINT 'It is a Valid JSON' –Valid JSON
	ELSE
	PRINT 'It is an Invalid JSON string'


	SELECT JSON_QUERY(@json, '$.Person');

	— Extract object/array value from a JSON text
	SELECT JSON_QUERY(@json, '$.Person[0].Address');

	— Returns NULL if the path does not exist
	SELECT JSON_QUERY(@json, '$.Person[0].Age');

	— lax option is by default and returns NULL if there is a problem
	SELECT JSON_QUERY(@json, 'lax $.Person[0].Age');

	— strict option raises an error if there is a problem
	SELECT JSON_QUERY(@json, 'strict $.Person[0].Age');

view raw

JSON_QUERY() Function in SQL Server 2016

hosted with ❤ by GitHub

You can control the return value of the JSON_QUERY function by specifying 2 types of mode –

Lax mode – Returns NULL if the path does not exist.
Strict mode – Returns an error if path does not exist.

When you execute the above query in SQL Server Management Studio, you will get the below results – showing how JSON_QUERY can help you to extract an object or array from a JSON string —

JSON_QUERY Results