JSON_VALUE function Sql

JSON_VALUE is one of the new JSON scalar function introduced in Sql Server 2016. This function returns the scalar value from the input JSON text from the specified JSON path location.

SYNTAX:

JSON_VALUE ( json_string,  json_path )

WHERE:

json_string is the JSON string from which the scalar value will be extracted.

json_path is the location of the scalar value in the json_string. Within json_path we can specify the path mode, it can be lax or strict. Lax is the default path mode, if json_path is invalid (i.e. it is not present in the json_string) then it returns null, but if path mode is strict it will raise an error.

[ALSO READ]:

Let us understand JSON_VALUE function with extensive list of examples:

Basic JSON_VALUE function examples

Example 1: Basic example where we are trying to get FirstName from a JSON string

SELECT 
 JSON_VALUE('{"FirstName":"Basavaraj","Last Name":"Biradar"}',
					'$.FirstName') AS 'First Name'

RESULT:

Here in the json_path the $ symbol implies the json_string and $.FirstName means FirstName property in the json_string at the root level.

Example 2: Getting FirstName JSON property value which is not at the root level

SELECT JSON_VALUE(
 '{"Name":{"FirstName":"Basavaraj","Last Name":"Biradar"}}',
					'$.Name.FirstName') AS 'First Name'

RESULT:

Here in this example FirstName property is not at the root level instead it is in the Name object, because of that we need to specify the path as ‘$.Name.FirstName’ instead of just ‘$.FirstName’ like the example 1.

Example 3: Repeat the example 2, but specify the path as in example one. Basically, the idea of this example is to check whether specifying the path like $.PropertyName searches the PropertyName anywhere in the JSON string or just at the root level

SELECT JSON_VALUE(
  '{"Name":{"FirstName":"Basavaraj","Last Name":"Biradar"}}',
					'$.FirstName') AS 'First Name'

RESULT:

From the result it is clear that, specifying the JSON path like $.PropertyName, just tries to locate the PropertName just at the root level, in otherwords JSON path is the exact location of the string.

Example 4: This example explains that the JSON path expression is case sensitive

SELECT JSON_VALUE('{"name":"Basavaraj"}','$.NAME') AS Name

RESULT:

From the result it is clear that the JSON path is case sensitive. Even though the JSON sting has the property name but the response returned is NULL because the property mentioned in the JSON path is in upper case where as in the JSON string it is in lower case

Let us try the above example by specifying the JSON path in the same case as that in the JSON string

SELECT JSON_VALUE('{"name":"Basavaraj"}','$.name') AS Name

RESULT:

Example 5: This example explains how to specify a JSON String property name with spaces in it in the JSON path expression

Let us try to fetch the JSON property Last Name value, where this property name has an empty space in it:

SELECT JSON_VALUE(
	'{"Last Name":"Basavaraj"}','$.Last Name') AS 'Last Name'

RESULT:

Msg 13607, Level 16, State 4, Line 1
JSON path is not properly formatted. Unexpected character found at position 7.

In case if the JSON property name has a space in it, then while specifying it in the JSON path we need to specify property name by enclosing it in the double quotes.

Let us re-write the query and try now

SELECT JSON_VALUE(
  '{"Last Name":"Basavaraj"}','$."Last Name"') AS 'Last Name'

RESULT:

Advanced JSON_VALUE function examples

Example 1: Reading values from JSON object where one of its property is an Array (i.e. [ , ])

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_VALUE(@json_str,'$.Name') Name,
	JSON_VALUE(@json_str,'$.Hobbies[0]') Hobby1,
	JSON_VALUE(@json_str,'$.Hobbies[1]') Hobby2

RESULT:

Example 2: Trying to get a non-scalar value. In the below example trying to get the value of a property which is an array

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_VALUE(@json_str,'$.Hobbies') Hobbies

RESULT:

From the result it is clear that if the JSON Path is pointing to a non-scalar value like an array, sub JSON object etc will return a NULL value or an error if PATH mode is strict. In this example the JSON path is pointing to an array [“Blogging”,”Cricket”] , because of this JSON_VALUE function returning a NULL value

Example 3: Reading JSON property value from a JSON object where Customers property is an array of JSON object (i.e. [{},{}])

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Customers":
	[{"Id":1,"Name":"Basavaraj",
			"Address":{"State":"KA","Country":"India"}},
	 {"Id":2,"Name":"Kalpana",
			"Address":{"State":"NY","Country":"United State"}}
	]
 }'
--Get first customer details
SELECT 
 JSON_VALUE(@json_str,'$.Customers[0].Name') Name,
 JSON_VALUE(@json_str,'$.Customers[0].Address.Country') Country
 --Get second customer details
SELECT 
 JSON_VALUE(@json_str,'$.Customers[1].Name') Name,
 JSON_VALUE(@json_str,'$.Customers[1].Address.Country') Country

RESULT:

Example 4: A modified version of the example 3

DECLARE @json_str NVARCHAR(MAX) = 
	'{"Customers":
		[{"Customer":{"Id":1,"Name":"Basavaraj",
			"Address":{"State":"KA","Country":"India"}}},
		 {"Customer":{"Id":2,"Name":"Kalpana",
			"Address":{"State":"NY","Country":"United State"}}}
		]
	}'
--Get first customer details
SELECT 
 JSON_VALUE(@json_str,'$.Customers[0].Customer.Name') Name,
 JSON_VALUE(@json_str,
   '$.Customers[0].Customer.Address.Country') Country
 --Get second customer details
SELECT 
 JSON_VALUE(@json_str,'$.Customers[1].Customer.Name') Name,
 JSON_VALUE(@json_str,
   '$.Customers[1].Customer.Address.Country') Country

RESULT:

[ALSO READ]:

SqlHints.com

By Basavaraj Biradar

Tag Archives: JSON_VALUE function Sql

JSON_VALUE Function in Sql Server 2016

Basic JSON_VALUE function examples

Advanced JSON_VALUE function examples

Basic JSON_VALUE function examples

Advanced JSON_VALUE function examples

Share this: