ISJSON Function in SQL Server 2016

By Samir Behara on September 23, 2017 • ( 3 )

Lot of web services and Web APIs are using JSON these days, primarily as their data interchange format. The inability of the older versions of SQL Server to parse, store and process JSON data has been a hindrance. This makes the integration of JSON tinto SQL Server as one of the most popular features of SQL Server 2016.

SQL Server 2016 provides built-in support for storing, managing and parsing JSON data. One important thing to note is that there is no separate JSON data type created, like XML – rather JSON is represented by NVARCHAR datatype.

In my earlier article, I explained how you can format query results as JSON using ‘FOR JSON‘ clause and how to transform JSON text to relational table using ‘OPENJSON‘ function.
BUILT-IN JSON SUPPORT IN SQL SERVER 2016

In this series of articles, I will explain the various JSON functions in SQL Server 2016 that can be used to handle JSON data —

JSON Functions SQL Server 2016

ISJSON() –
When you receive a JSON object from the front end client application, you can do a quick verification in SQL Server side to ensure that the JSON is valid, before storing it into the database. It is always a safe idea to validate the JSON text, before parsing JSON data.

OPENJSON function can be used to convert JSON text into table rows and columns. Before using this function, it is better to validate the correctness of the JSON text.

ISJSON Function

Let us now see a demonstration of the ISJSON() function –

	–ISJSON() – To verify that the text has valid JSON data
	DECLARE @json nvarchar(max)
	SET @json =N'
	{
	"Person": [
	{
	"FirstName": "Gustavo",
	"LastName": "Achong",
	"AccountNumber": "AW00029484",
	"ModifiedDate": "2014-09-12T11:15:07.263"
	}
	]
	}'

	IF (ISJSON(@json) = 1)
	PRINT 'It is a Valid JSON' –Valid JSON
	ELSE
	PRINT 'It is an Invalid JSON string'

view raw

ISJSON() Function in SQL Server 2016 – Valid JSON object

hosted with ❤ by GitHub

When you execute the above query in SQL Server Management Studio, you will get the below message stating that it is a valid JSON string —

Is Valid JSON

	–ISJSON() – To verify that the text has valid JSON data
	DECLARE @json nvarchar(max)
	SET @json =N'
	{
	"Person": [
	{
	"FirstName": "Gustavo",
	"LastName": "Achong",
	"AccountNumber": "AW00029484",
	"ModifiedDate" "2014-09-12T11:15:07.263"
	}
	]
	}'

	IF (ISJSON(@json) = 1)
	PRINT 'It is a Valid JSON'
	ELSE
	PRINT 'It is an Invalid JSON string' — Invalid JSON

view raw

ISJSON() Function in SQL Server 2016 – Invalid JSON object

hosted with ❤ by GitHub

When you execute the above query in SQL Server Management Studio, you will get the below message stating that it is an invalid JSON string, since I knowingly missed the ‘:’ while specifying the ModifiedDate field —

Is Invalid JSON Object