Playing around with the new capabilities of SQL Server 2016 has been an exciting journey. Even after a year, I continue to discover new features and functionalities every single day that I work with it. In a previous blog post, I discussed the concept of NULL values in JSON and how they are returned. Little did I know that there are even more interesting aspects to explore in this area.

One of the answers provided in response to my previous blog post got me intrigued and motivated me to write the next article in this series. This was a learning experience for me as well. While working with JSON, I discovered a keyword that can work wonders – it’s called STRICT.

Let’s take a look at how the same code from the previous blog post can be modified to use the STRICT keyword:

DECLARE @myjson NVARCHAR(MAX) =
'{
	"Name": "Pinal",
	"Surname": "Kumar",
	"Birth": {"DOB":"2000-12-12" , "Town":"Gandhi Nagar", "Country":"India"}
}'

SELECT JSON_VALUE(@myjson, 'strict $.name')

When executing this code, you will notice that instead of getting a NULL value as discussed before, we now receive an explicit error message that represents the actual problem:

Msg 13608, Level 16, State 5, Line 1
Property cannot be found on the specified JSON path.

This error message helps us identify the issue more accurately. However, it’s important to note that while experimenting with the NULL value, I discovered that if the returned string value is larger than VARCHAR (4000), there will be an error when using the STRICT keyword:

Msg 13625, Level 16, State 1, Line 1
String value in the specified JSON path would be truncated.

The learning never stops when working with SQL Server. I would love to hear how you are utilizing JSON datatypes in your projects. Please share your experiences and insights in the comments section below.