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 —
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
|— JSON_QUERY Demonstration to extract an object or array from the JSON data|
|DECLARE @json nvarchar(max)|
|SET @json =N'|
|IF (ISJSON(@json) = 1)|
|PRINT 'It is a Valid JSON' –Valid JSON|
|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.Address');|
|— Returns NULL if the path does not exist|
|SELECT JSON_QUERY(@json, '$.Person.Age');|
|— lax option is by default and returns NULL if there is a problem|
|SELECT JSON_QUERY(@json, 'lax $.Person.Age');|
|— strict option raises an error if there is a problem|
|SELECT JSON_QUERY(@json, 'strict $.Person.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 —
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
Leave a Reply