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 —

— JSON_QUERY Demonstration to extract an object or array from the JSON data
DECLARE @json nvarchar(max)
SET @json =N'
{
"Person": [
{
"Name": {
"FirstName": "Catherine",
"LastName": "Abel"
},
"Address": {
"State": "VA",
"Zip": "24153"
}
}
]
}'
IF (ISJSON(@json) = 1)
PRINT 'It is a Valid JSON' –Valid JSON
ELSE
PRINT 'It is an Invalid JSON string'
SELECT JSON_QUERY(@json, '$.Person');
— Extract object/array value from a JSON text
SELECT JSON_QUERY(@json, '$.Person[0].Address');
— Returns NULL if the path does not exist
SELECT JSON_QUERY(@json, '$.Person[0].Age');
— lax option is by default and returns NULL if there is a problem
SELECT JSON_QUERY(@json, 'lax $.Person[0].Age');
— strict option raises an error if there is a problem
SELECT JSON_QUERY(@json, 'strict $.Person[0].Age');

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.



Categories: SQL Server, SQL Server 2016

1 reply

Trackbacks

  1. JSON_MODIFY FUNCTION IN SQL SERVER 2016 – dotnetvibes

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: