JSON_VALUE Function in SQL Server 2016

By Samir Behara on September 24, 2017 • ( 3 )

In my previous blog post, we looked at the ISJSON() Function and how it can help you to do a quick verification in SQL Server side to ensure that the JSON is valid, before storing it into the database.

OPENJSON function can be used to convert JSON text into table rows and columns or to import JSON object into SQL tables.

However sometimes we might need to just extract one scalar value from the JSON data, instead of parsing and returning the entire data. In such cases, we can use a new function in SQL Server 2016 called JSON_VALUE().

JSONVALUE Function

You can control the return value of the JSON_VALUE 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.

Let us look at a demonstration of the JSON_VALUE() and also understand how the LAX/STRICT mode works —

	— JSON_VALUE Demonstration to pull one scalar value from the JSON data
	DECLARE @json nvarchar(max)
	SET @json =N'
	{
	"Person": [
	{
	"FirstName": "Gustavo",
	"LastName": "Harris",
	"AccountNumber": "AW00029484",
	"ModifiedDate": "2014-09-12T11:15:07.263"
	},
	{
	"FirstName": "Catherine",
	"LastName": "Abel",
	"AccountNumber": "AW00029485",
	"ModifiedDate": "2014-09-12T11:15:07.263"
	}
	]
	}'

	— Extract scalar value from a JSON text
	SELECT JSON_VALUE(@json, '$.Person[0].LastName');
	SELECT JSON_VALUE(@json, '$.Person[1].AccountNumber');

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

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

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

view raw

JSON_VALUE() Function in SQL Server 2016

hosted with ❤ by GitHub

We executed 5 T-SQL queries above, and as expected the first 2 statements return a valid result extracting the scalar values from the Person object.

The LAX mode is the default and return NULL if the path does not exist. Since there is no node named as ‘Age’ in the Person object, it returns a NULL.

JSON_VALUE Function

The STRICT mode however returns an error if there is an error with the path. If you need to return an error message back to the client side if the path is not mentioned correctly, the STRICT mode is what you need.

JSON_VALUE Error