Post
Ask Question

Validate JSON With ISJSON() Function In SQL Server 2016

This is one of the new features of SQL Server 2016. Before this article, I would like you to go through one of the previous articles on the Split_String function in SQL Server 2016.

I hope you love this new feature of SQL Server. Let’s move on to the ISJSON() function.

What is JSON?

JSON (JavaScript Object Notation) is a text based format, used for data exchange. It is lightweight compared to XML and thus is preferred as a best choice to transfer the data across the devices. It is open and readable. We can parse JSON by JavaScript implementations. Because of a few of these features, the developers prefer JSON over XML these days.

JSON Syntax

Here, I will show what JSON looks like:

  1. {"students":[  
  2. {"Name":"James", "Age":27},  
  3. {"Name":"John Doe", "Age":28},  
  4. {"Name":"John Smith", "Age":29}  
  5. ]}  
SQL Server 2016 has introduced a new function that can validate whether the input passed is JSON or not. If JSON is correct, the value is 1 and that would be TRUE,  and if it returns 0 then it means there is something wrong with the JSON format and it is False.

Let us see how this function works. Open SQL Server 2016 and write the following script in the Query Editor.
  1. DECLARE @JSONINPUT NVARCHAR(4000)  
  2. SET @JSONINPUT = N'{"students":[  
  3. {"Name":"James", "Age":27},  
  4. {"Name":"John Doe", "Age":28},  
  5. {"Name":"John Smith", "Age":29}  
  6. ]}'  
  7. IF (ISJSON(@JSONINPUT) = 1)  
  8. BEGIN  
  9. PRINT 'This is JSON!!'  
  10. END  
  11. ELSE  
  12. BEGIN  
  13. PRINT 'NOT a JSON!!'  
  14. END  
  15. GO  
Let us execute the script and see if we get the correct output or not. We should be able to see “This is JSON” as the output.

output
Thus, we get the expected output. Let us make some modifications to JSON that we have passed as an input to the function. I’ll remove one of the brackets from JSON and check for the output. Let us see what happens:
  1. DECLARE @JSONINPUT NVARCHAR(4000)  
  2. SET @JSONINPUT = N '{"students":[   
  3. {  
  4. "Name": "James",  
  5. "Age": 27  
  6. },   
  7. {  
  8. "Name": "John Doe",  
  9. "Age": 28  
  10. }, "Name": "John Smith", "Age": 29  
  11. }]  
  12. }  
  13. '  
  14. IF(ISJSON(@JSONINPUT) = 1)  
  15. BEGIN  
  16. PRINT 'This is JSON!!'  
  17. END  
  18. ELSE  
  19. BEGIN  
  20. PRINT 'NOT a JSON!!'  
  21. END  
  22. GO  
Execute the preceding script.

output
Since JSON is not valid, the function returns an invalid JSON message. This validates the functionality of ISJSON() function in SQL Server 2016.

Let us pass null into the function and check what will be the output:

output
We get null as the output. For null, this function will return null as the output.

Summary

In this post, we saw a very new feature i.e., ISJSON() function and how it can be used. This is very helpful for the developers as the same functionality can be used in SQL itself.


Similar Articles