Tags
ISJSON Function, ISJSON function in Sql, ISJSON function in Sql Server, ISJSON function in Sql Server 2016, ISJSON function Sql, JSON, JSON Functions, JSON Functions in Sql, JSON Functions in Sql Server, JSON Functions in Sql Server 2016, JSON in Sql, JSON in Sql Server, JSON in Sql Server 2016, JSON Sql, SQL, Sql 2016, Sql ISJSON, SQL Server, Sql Server 2016, Sql Server 2016ISJSON
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
Improving my SQL BI Skills 