JSON_QUERY FUNCTION IN SQL SERVER 2016


SQL Server 2016 provides built-in support for storing, managing and parsing JSON data. In my previous blog posts, we looked at ISJSON and JSON_VALUE function in details.

In this article, we will look at another new function in SQL Server 2016 – JSON_QUERY which you can use to extract an object or array from a JSON string.

JSON_QUERY Function

This might look pretty similar to JSON_VALUE function. However it has a distinct difference in its use case.

JSON_VALUE function returns a scalar value whereas JSON_QUERY returns an object or an array from the JSON data.

Let us now see a demonstration of the JSON_QUERY function in details —

You can control the return value of the JSON_QUERY 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.

When you execute the above query in SQL Server Management Studio, you will get the below results – showing how JSON_QUERY can help you to extract an object or array from a JSON string —

JSON_QUERY Results

When strict option is used and the path does not exists, then an error message is shown–

JSON_QUERY Messages

In my upcoming blog posts, we will go through the other JSON Functions in detail.

One thought on “JSON_QUERY 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