JSON_VALUE Function in SQL Server 2016


In my previous blog post, we looked at the ISJSON() Function and how it can help you to do a quick verification in SQL Server side to ensure that the JSON is valid, before storing it into the database.

OPENJSON function can be used to convert JSON text into table rows and columns or to import JSON object into SQL tables. 

However sometimes we might need to just extract one scalar value from the JSON data, instead of parsing and returning the entire data. In such cases, we can use a new function in SQL Server 2016 called JSON_VALUE().

JSONVALUE Function

You can control the return value of the JSON_VALUE function by specifying 2 types of mode –
 
Lax mode – Returns NULL if the path does not exist.
Strict mode – Returns an error if path does not exist.

Let us look at a demonstration of the JSON_VALUE() and also understand how the LAX/STRICT mode works —

We executed 5 T-SQL queries above, and as expected the first 2 statements return a valid result extracting the scalar values from the Person object.

The LAX mode is the default and return NULL if the path does not exist. Since there is no node named as ‘Age’ in the Person object, it returns a NULL.

JSON_VALUE Function

The STRICT mode however returns an error if there is an error with the path. If you need to return an error message back to the client side if the path is not mentioned correctly, the STRICT mode is what you need.

JSON_VALUE Error

In my upcoming blog posts, we will look at other JSON Functions() in SQL Server 2016 – JSON_QUERY() and JSON_MODIFY().

3 thoughts on “JSON_VALUE Function in SQL Server 2016

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s