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.