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.


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 —


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


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

Categories: SQL Server, SQL Server 2016

1 reply


  1. JSON_MODIFY FUNCTION IN SQL SERVER 2016 – dotnetvibes

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: