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
You can use variables for both arguments of the JSON_VALUE function in SQL Server 2017 even if the database is still in compatibility mode 130 (which corresponds to SQL Server 2016).
There are not many differences between JSON implementations in SQL Server 2016 and 2017; this is the most important one.