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

• Expression: This is  JSON text in the Unicode format.
• Path: This is an optional argument; it is a JSON path expression and you can use it to specify a fragment of the input expression.

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 in SQL Server 2016 is that a variable as a second argument (JSON path) is not allowed. For instance, the following code won’t work in SQL Server 2016:

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 was a significant limitation; you had to provide JSON path as a static value in advance and you cannot add or change it dynamically. Fortunately, this limitation has been removed in SQL Server 2017, and the preceding code in SQL Server 2017 provides the following result:

album
------------------
Wish You Were Here

There are not many differences between JSON implementations in SQL Server 2016 and 2017; this is the most important one.