The JSON_QUERY function extracts a JSON fragment from the input JSON string for the specified JSON path. It returns a JSON object or an array; therefore, its output is JSON conforming. This function is complementary to the JSON_VALUE function.

JSON_QUERY always returns JSON conforming text. Thus, if you want to suggest to SQL Server that the string is JSON formatted, you should wrap it with this function.

The function has two input arguments:

• Expression: This is a variable or column containing JSON text.
• Path: This is a JSON path that specifies the object or the array to extract. This parameter is optional. If it's not specified, the whole input string will be returned.

The return type of the function is nvarchar(max) if the input string is defined as (n)varchar(max); otherwise, it is nvarchar(4000). As already mentioned, the function always returns a JSON conforming string.

If either the expression or the path is not valid, JSON_QUERY returns an error message saying that the JSON text or JSON path is not properly formatted.

In the following self-explanatory examples, how to use this function with different JSON path expressions is demonstrated:

DECLARE @json NVARCHAR(MAX) = N'{ 
"Album":"Wish You Were Here", 
"Year":1975, 
"IsVinyl":true, 
"Songs" :[{"Title":"Shine On You Crazy Diamond","Writers":"Gilmour, Waters, Wright"}, 
{"Title":"Have a Cigar","Writers":"Waters"}, 
{"Title":"Welcome to the Machine","Writers":"Waters"}, 
{"Title":"Wish You Were Here","Writers":"Gilmour, Waters"}], 
"Members":{"Guitar":"David Gilmour","Bass Guitar":"Roger Waters","Keyboard":"Richard Wright","Drums":"Nick Mason"} 
}'; 
--get Songs JSON fragment (array) 
SELECT JSON_QUERY(@json,'$.Songs'); 
--get Members SON fragment (object) 
SELECT JSON_QUERY(@json,'$.Members'); 
--get fourth Song JSON fragment (object) 
SELECT JSON_QUERY(@json,'$.Songs[3]'); 

Here is the result of these invocations:

[{"Title":"Shine On You Crazy Diamond","Writers":"Gilmour, Waters,  
   Wright"},
{"Title":"Have a Cigar","Writers":"Waters"},
{"Title":"Welcome to the Machine","Writers":"Waters"},
{"Title":"Wish You Were Here","Writers":"Gilmour, Waters"}]
{"Guitar":"David Gilmour","Bass Guitar":"Roger 
  Waters","Keyboard":"Richard Wright","Drums":"Nick Mason"}
{"Title":"Wish You Were Here","Writers":"Gilmour, Waters"}

You can see that the returned values are JSON objects and arrays. However, if you specify a value that is not an array or object, the function returns NULL in lax mode and an error in strict mode:

--get property value (number) 
SELECT JSON_QUERY(@json,'$.Year'); 
--get property value (string) 
SELECT JSON_QUERY(@json,'$.Songs[1].Title'); 
--get value for non-existing property 
SELECT JSON_QUERY(@json,'$.Studios'); 

All three calls return NULL, whereas strict mode raises a batch-level exception:

SELECT JSON_QUERY(@json,'strict $.Year'); 
/*Result: 
Msg 13624, Level 16, State 1, Line 54 
Object or array cannot be found in the specified JSON path. 
*/ 
--get value for non-existing property 
SELECT JSON_QUERY(@json,'strict $.Studios'); 
/*Result: 
Msg 13608, Level 16, State 5, Line 60 
Property cannot be found on the specified JSON path 
*/ 

You can also use JSON_QUERY to ensure data integrity of JSON data in a table column. For instance, the following check constraint ensures that all persons in the People table have the OtherLanguages property within the CustomFields column if this column has a value:

USE WideWorldImporters; 
ALTER TABLE Application.People 
ADD CONSTRAINT CHK_OtherLanguagesRequired 
CHECK (JSON_QUERY(CustomFields, '$.OtherLanguages') IS NOT NULL OR CustomFields IS NULL); 

The JSON_QUERY function has the same restrictions in SQL Server 2016 for the path argument as JSON_VALUE; only literals are allowed. In SQL Server 2017 you can use variables too.