The JSON_VALUE function extracts a scalar value from a JSON string. It accepts two input arguments:

The return type of the function is nvarchar(4000), with the same collation as in the input expression. If the extracted value is longer than 4,000 characters, the function returns NULL provided the path is in lax mode or an error message in the case of strict mode.

If either the expression or the path is not valid, the JSON_VALUE function returns an error explaining that the JSON text is not properly formatted.

The following example shows the JSON_VALUE function in action. It is used to return values for properties and an array element:

DECLARE @json NVARCHAR(MAX) = N'{ 
"Album":"Wish You Were Here", 
"Year":1975, 
"IsVinyl":true, 
"Members":["Gilmour","Waters","Wright","Mason"] 
}'; 
SELECT  
  JSON_VALUE(@json, '$.Album') AS album, 
  JSON_VALUE(@json, '$.Year') AS yr, 
  JSON_VALUE(@json, '$.IsVinyl') AS isVinyl, 
  JSON_VALUE(@json, '$.Members[0]') AS member1; 

Here is the result of the previous query:

album                  yr   isVinyl  member1 
-------------------  -----  ------   --------
Wish You Were Here    1975  true    Gilmour

Note that all returned values are strings; as already mentioned, the data type of the returned value is nvarchar.

The aim of the function is to extract scalar values. Therefore, it won't work if the JSON path specifies an array or an object. The following call with the JSON string in the previous example will return a NULL value:

DECLARE @json NVARCHAR(MAX) = N'{ 
"Album":"Wish You Were Here", 
"Year":1975, 
"IsVinyl":true, 
"Members":["Gilmour","Waters","Wright","Mason"] 
}'; 
SELECT  
  JSON_VALUE(@json, '$.Members') AS member; 

The JSON path $.members specifies an array and the function expects a scalar value. A null value will be returned even if the property specified with the path expression does not exist. As mentioned earlier, the JSON path expression has two modes: lax and strict. In the default lax mode, errors are suppressed and functions return NULL values or empty tables, while every unexpected or non-existing path raises a batch-level exception. The same call with the JSON path in strict mode would end up with an error:

SELECT  
  JSON_VALUE(@json, 'strict $.Members') AS member; 

Here is the error message:

Msg 13623, Level 16, State 1, Line 75
Scalar value cannot be found in the specified JSON path.

If the length of a JSON property value or string element is longer than 4,000, the function returns NULL. The next example demonstrates this by using two very long strings as values for two properties. The first one has 4,000 characters and the second is one character longer:

DECLARE @json NVARCHAR(MAX) = CONCAT('{"name":"', REPLICATE('A',4000), '",}'), 
@json4001 NVARCHAR(MAX) = CONCAT('{"name":"', REPLICATE('A',4001), '",}')  
SELECT  
  JSON_VALUE(@json, '$.name') AS name4000, 
  JSON_VALUE(@json4001, '$.name') AS name4001; 

The abbreviated result is here:

Name4000             name4001
-------------------  ---------
AAAAAAAAAAAAAAAA...  NULL

You can see that 4001 is too much for JSON_VALUE, and the function returns NULL. If you specify strict in the previous example, the function returns an error:

DECLARE @json4001 NVARCHAR(MAX) = CONCAT('{"name":"', REPLICATE('A',4001), '",}')  
SELECT  
  JSON_VALUE(@json4001, ' strict $.name') AS name4001; 
 

Here is the error message:

Msg 13625, Level 16, State 1, Line 65
String value in the specified JSON path would be truncated.

This is a typical change in function behavior regarding the JSON path mode. Lax mode usually returns NULL and does not break the code, while strict mode raises a batch-level exception.

JSON_VALUE can be used in SELECT, WHERE, and ORDER clauses. In the following example, it is used in all three clauses:

SELECT  
  PersonID, 
  JSON_VALUE(UserPreferences, '$.timeZone') AS TimeZone, 
  JSON_VALUE(UserPreferences, '$.table.pageLength') AS PageLength 
FROM Application.People 
WHERE JSON_VALUE(UserPreferences, '$.dateFormat') = 'yy-mm-dd' 
  AND JSON_VALUE(UserPreferences, '$.theme') = 'blitzer' 
ORDER BY JSON_VALUE(UserPreferences, '$.theme'), PersonID; 

One important limitation of the JSON_VALUE function is that a variable as a second argument (JSON path) is not allowed. For instance, the following code won't work:

DECLARE @jsonPath NVARCHAR(10) = N'$.Album'; 
DECLARE @json NVARCHAR(200) = N'{ 
"Album":"Wish You Were Here", 
"Year":1975 
}'; 
SELECT  
  JSON_VALUE(@json, @jsonPath) AS album;  

The query fails with the following error message:

Msg 13610, Level 16, State 1, Line 137
The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal

This is a significant limitation; you need to provide the JSON path as a static value in advance and you cannot add or change it dynamically. If you think that this function would be more useful without this limitation, then you can vote for the related item Don't restrict JSON_VALUE and JSON_QUERY to string literals only for the path at the following web address: https://connect.microsoft.com/SQLServer/Feedback/Details/2235470.

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:

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); 

JSON_QUERY has the same restriction for the path argument as JSON_VALUE; only literals are allowed.