Monday, November 7, 2016

Is there a JSON validator in SQL Server?

Sure there is if you are using SQL Server 2016.

The Transact-SQL IsJSON() tests whether a expression(string) is JSON valid. If it is valid you should get a 1 as return value, a zero(0) if it is not valid and a null if the expression is null.

How do you use it?

This is a json string, a very simple one:
=================
{"wclass":{"student":["jay", "john", "sam"]}}
====================
The following code snippet shows how you may use it:
===========
declare @json nvarchar(150)SET @json=N'{"wclass":{"student":["jay", "john", "sam"]}}';
Select ISJSON(@json)

==========
When you run this in the SQL Server 2016 query pane, you get the return value 1 (see image below).


No comments: