NEW T-SQL FEATURES IN SQL SERVER 2016 – XVI | JSON_QUERY()

In this post we shall check out the new features Microsoft introduced in SQL Server 2016 | JSON_QUERY()

New feature – XVI | JSON_QUERY()


This function is used to extracts an object or an array from a JSON string.

Syntax

--

JSON_QUERY ( expression [ , path ] )  

--

Notes

1. If JSON_QUERY finds JSON that is not valid in expression before it finds the value identified by path, the function returns an error.

2. If JSON_QUERY doesn’t find the value identified by path, it scans the entire text and returns an error if it finds JSON that is not valid anywhere in expression.

3. The function returns a JSON fragment of type nvarchar(max).

4. The collation of the returned value is the same as the collation of the input expression.If the value is not an object or an array:
1. In lax mode, JSON_QUERY returns null.
2. In strict mode, JSON_QUERY returns an error.

/*Returns all data if path is not specified*/

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,
		"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
SELECT JSON_QUERY(@j)
GO


--------------------------------------------------------------------------
{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,
		"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}

(1 row affected)


--

/*Returns all data for SkillSet (Valid PATH) */

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,
		"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
SELECT JSON_QUERY(@j,'$.SkillSet')
GO


----------------------------------
["SQL","SSIS","SSRS","SSAS"]

(1 row affected)

--

/*Returns all data for SkillSet (Invalid Path)*/

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,
		"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
SELECT JSON_QUERY(@j,'$.SkillSe')
GO


---------------
NULL

(1 row affected)


--

/*Returns all data for SkillSet (Invalid Path) – STRICT MODE */

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,
		"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
SELECT JSON_QUERY(@j,'strict$.Pawan')
GO


----------------------------------------------------------
Msg 13608, Level 16, State 2, Line 55
Property cannot be found on the specified JSON path.


--

/*Returns all data for SkillSet (Invalid Path) – LAX MODE */

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,
		"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
SELECT JSON_QUERY(@j,'lax$.Pawan')
GO


---------------------------------
NULL

(1 row affected)

--

Refer Microsoft LINK below for more details-

1. https://docs.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com