Tags

, , , , , , , , , , , , , , , , , , ,


NEW T-SQL FEATURES IN SQL SERVER 2016 – XIII | ISJSON()

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

New feature – XIII | ISJSON()


This function checks whether a string contains valid JSON or NOT.

Notes

1. In SQL Server 2016 JSON will be represented as NVARCHAR type.
2. Example of valid JSON

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

3. This function returns 1 if the string contains valid JSON; otherwise, returns 0. Returns null if expression is null.
4. ISJSON does not check the uniqueness of keys at the same level.
5. ISJSON does not does not return any errors.

/*Example of Valid JSON*/

--

SELECT ISJSON(
'{
	 "name":"Pawan"
	,"MiddleName":"Kumar"
	,"Surname":"Khowal"
	,"Class":10
	,"SkillSet":["SQL","SSIS","SSRS","SSAS"]
	,"IsDatabaseDeveloper":true
 }')

OUTPUT
 
-----------
1

(1 row affected)


--

/*Example of InValid JSON*/

--

SELECT ISJSON(
'{
	 Kumar
 }')

OUTPUT

-----------
0

(1 row affected)


--

/*Example of NULL JSON*/

--

SELECT ISJSON(NULL)

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

(1 row affected)


--

Usage

/*Example of ISJSON Usage – 1*/

--

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

IF (ISJSON(@param) > 0)  
BEGIN  
     -- Do something 
END

--

/*Example of ISJSON Usage – 2*/

--

SELECT *
FROM table1
WHERE ISJSON(JSONColumn) > 0 

--

Related Post

1. https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xii-openjson-function-sql-server/

Refer Microsoft LINK below for more details-

1. https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements