Tag Archives: State 2

JSON_QUERY Function in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , , , , , , , , , ,

JSON_QUERY is one of the new JSON function introduced in Sql Server 2016 to query the JSON data. JSON_QUERY basically returns the JSON fragment (i.e. JSON object or an array) from the input JSON string from the specified JSON path.

SYNTAX:

JSON_QUERY ( json_string,  json_path )

WHERE:

json_string is the JSON string from which the JSON fragment will be extracted.

json_path is the location of the JSON string 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.

This function will return error even in the scenario if the specified json_path is resulting in a scalar value other than the JSON object or array. Where as JSON_VALUE works the other way, it returns an error in case the JSON path is

[ALSO READ]:

Let us understand JSON_QUERY function with extensive list of examples:

Example 1: In this example let us try to get the Hobbies array using the JSON_QUERY function

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

RESULT:
JSON_QUERY Sql Example 1 1

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

Let us try doing the same using the JSON_VALUE function

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

RESULT:
JSON_QUERY Sql Example 1 2

From the result it is clear that JSON_VALUE function is not for reading the JSON object or array, instead it is for reading the scalar JSON values like string, integer etc.

Example 2: Try to get the complete JSON string from the root

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
--Get customer details
SELECT JSON_QUERY(@json_str,'$') JSON
GO

RESULT:
JSON_QUERY Sql Example 2

Example 3: Try to get a JSON scalar value (i.e. non-JSON object or array)

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

RESULT:
JSON_QUERY Sql Example 3
[ALSO READ]:lax and strict JSON Path modes in Sql Server 2016
Example 4: lax and strict JSON path modes in JSON_QUERY

Let us try to re-execute the example 3 by setting the JSON path mode as strict

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

RESULT:

Msg 13608, Level 16, State 2, Line 4
Property cannot be found in specified path.

Let us try executing the above query by explicitly specifying the default JSON path mode lax

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

RESULT:
JSON_QUERY Sql Example 4 2
Example 5: In this example try to read on of the array element which in-turn is not a JSON object or an array

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

RESULT:
JSON_QUERY Sql Example 5 1

Try to execute the above query using the JSON_VALUE function instead of JSON_QUERY function

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

RESULT:
JSON_QUERY Sql Example 5 2

From these examples it is clear that we can use the JSON_QUERY function to extract a JSON object or an array only, but not for the scalar values like string, integer etc.

Example 6: Try to extract complete JSON from the JSON string

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"}}
	]
 }'
 SELECT JSON_QUERY(@json_str,'$') CompleteJSON

RESULT:
JSON_QUERY Sql Example 6

Example 7: In this example try to extract the Customers array

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"}}
	]
 }'
 SELECT JSON_QUERY(@json_str,'$.Customers') CustomersArray

RESULT:
JSON_QUERY Sql Example 7

Example 8: In this example extract the first customer JSON object

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"}}
	]
 }'
 SELECT JSON_QUERY(@json_str,'$.Customers[0]') CustomerObject

RESULT:
JSON_QUERY Sql Example 8

Example 9: In this example try to extract the first customer’s address object

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"}}
	]
 }'
 SELECT JSON_QUERY(@json_str,'$.Customers[0].Address') AddressObject

RESULT:
JSON_QUERY Sql Example 9

[ALSO READ]: