Tag Archives: Level 16

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]:

Recursive CTE – Sql Server

Sql Server, , , , , , , , , , , , , , , , , , , , , , , , ,

This is the second article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series:

Introduction to Common Table Expression (a.k.a CTE)
Multiple CTEs in a Single Query
Nested Common Table Expressions

Recursive Common Table Expression

CTEs provide a mechanism to write easy to understand, more readable and maintainable recursive queries. Prior to CTEs only mechanism to write recursive query is by means of recursive function or stored procedure. And these recursive functions or stored procedures support only up-to 32 levels of recursion. By default CTEs support a maximum recursion level of 100. CTEs also provide an option to set a MAXRECURSION level value between 0 to 32,767. Specifying it’s value as 0 means no limit to the recursion level, you agreed for a risk in case of a poorly written query resulting in infinite recursion level.

Recursive CTE Example:

Below is a very basic example of a recursive CTE generating numbers between 1 to 10:

RecursiveCTE Basic Example

WITH NumbersCTE AS
(
	SELECT  1 AS Number
	UNION ALL
	SELECT Number + 1 FROM NumbersCTE
	WHERE Number < 10
 )
SELECT * FROM NumbersCTE

RESULT:
Recursive CTE Basic Example result

Example of Recursive CTE to get Managers and employees reporting to them

This example uses the Employees table created in the previous article Introduction to Common Table Expression (a.k.a CTE). You can visit the link to create the table, if you have not created it already.

WITH EmpRecursiveCTE(ManagerId, EmployeeId, Name, Level) AS 
(
    SELECT ManagerId, Id, Name, 0 AS Level
    FROM dbo.Employees 
    WHERE ManagerId IS NULL
		UNION ALL
    SELECT E.ManagerId, E.Id, E.Name, Level + 1
    FROM dbo.Employees E
        INNER JOIN EmpRecursiveCTE EmpCTE
        ON E.ManagerID = EmpCTE.EmployeeId 
)
SELECT EmployeeId, Name, ManagerID, Level 
FROM EmpRecursiveCTE
ORDER BY ManagerID

RESULT:
Recursive CTE Basic Example result Employee Hierarchy

MAXRECURSION hint in Recursive CTE

By default maximum recursion level supported by CTE is 100. But CTE provides an option to change it by means of the MAXRECURSION hint. MAXRECURSION hint value can be between 0 to 32,767. Specifying it’s value as 0 means no limit.

Let us understand MAXRECURSION hint in a Recursive CTE by the below examples:

CTEs default maximum recursion level

Try to generate numbers between 1 to 200 by using a recursive CTE by the following script:

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE
    WHERE Number < 200
 )
SELECT * FROM NumbersCTE

RESULT:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

From the above result it is clear that, the CTEs default maximum recursion level is 100.

How to change CTEs default maximum recursion level?

We can change the CTEs default maximum recursion by specifying the MAXRECURSION query hint. Change the previous recursive CTE to generate numbers between 1 to 200 by specifying the MAXRECURSION hint value as 210 as below and verify the result:

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE
    WHERE Number < 200
 )
SELECT * FROM NumbersCTE
OPTION (MAXRECURSION 210)

RESULT:
Recursive CTE with MAXRECURSION hint

From the above result it is clear that we can change the CTEs default maximum recursion by means of MAXRECURSION query hint.

MAXRECURSION query hint is helpful to terminate the poorly written recursive CTE

MAXRECURSION hint is very helpful in a scenario where poorly written CTE is resulting in an infinite recursion level, in such cases MAXRECURSION terminates the CTE once the defined recursion crosses. For example below is an infinite recursive CTE, but because of the MAXRECURSION hint value 210 it terminates the CTE execution once it reaches the recursion level of 210.

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE   
 )
SELECT * FROM NumbersCTE
OPTION (MAXRECURSION 210)

RESULT:
Infinite Recursive CTE with MAXRECURSION hint
Maximum recursion level that we can specify with MAXRECURSION query hint is 32,767

Try to generate the numbers between 1 to 40000 by the below script, by specifying the MAXRECURSION query hint value as 40000.

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE  
	WHERE  Number < 40000
 )
SELECT * FROM NumbersCTE
OPTION (MAXRECURSION 40000)

RESULT:

Msg 310, Level 15, State 1, Line 9
The value 40000 specified for the MAXRECURSION option exceeds the allowed maximum of 32767.

From the above result it is clear that the maximum recursion level we can specify with the MAXRECURSION query hint is 32,767.

How to support the recursion level greater than the maximum recursion level 32,767?

If we come across a scenario, where we need to have recursion level greater than 32,767, in such scenarios we can achieve this by specifying MAXRECURSION value as 0. MAXRECURSION query hint value 0 means no limit to the recusion level, if we are specifying this we should make sure that our query is not resulting in an infinite recursion level. So, we can re-write the previous recursive CTE as below:

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE  
	WHERE  Number < 40000
 )
SELECT * FROM NumbersCTE
OPTION (MAXRECURSION 0)

RESULT:
Recursive CTE MAXIMUM Recursion Level 0