JSON_VALUE (Transact-SQL)
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
Extracts a scalar value from a JSON string.
To extract an object or an array from a JSON string instead of a scalar value, see JSON_QUERY (Transact-SQL). For info about the differences between JSON_VALUE
and JSON_QUERY
, see Compare JSON_VALUE and JSON_QUERY.
Transact-SQL syntax conventions
JSON_VALUE ( expression , path )
An expression. Typically the name of a variable or a column that contains JSON text.
If JSON_VALUE
finds JSON that is not valid in expression before it finds the value identified by path, the function returns an error. If JSON_VALUE
doesn't find the value identified by path, it scans the entire text and returns an error if it finds JSON that is not valid anywhere in expression.
A JSON path that specifies the property to extract. For more info, see JSON Path Expressions (SQL Server).
In SQL Server 2017 (14.x) and in Azure SQL Database, you can provide a variable as the value of path.
If the format of path isn't valid, JSON_VALUE
returns an error.
Returns a single text value of type nvarchar(4000). The collation of the returned value is the same as the collation of the input expression.
If the value is greater than 4000 characters:
In lax mode,
JSON_VALUE
returnsNULL
.In strict mode,
JSON_VALUE
returns an error.
If you have to return scalar values greater than 4000 characters, use OPENJSON
instead of JSON_VALUE
. For more info, see OPENJSON (Transact-SQL).
JSON functions work the same whether the JSON document is stored in varchar, nvarchar, or the native json data type.
Consider the following JSON text:
DECLARE @jsonInfo NVARCHAR(MAX)
SET @jsonInfo=N'{
"info":{
"type":1,
"address":{
"town":"Bristol",
"county":"Avon",
"country":"England"
},
"tags":["Sport", "Water polo"]
},
"type":"Basic"
}'
The following table compares the behavior of JSON_VALUE
in lax mode and in strict mode. For more info about the optional path mode specification (lax or strict), see JSON Path Expressions (SQL Server).
Path | Return value in lax mode | Return value in strict mode | More info |
---|---|---|---|
$ | NULL |
Error | Not a scalar value. Use JSON_QUERY instead. |
$.info.type | N'1' | N'1' | N/a |
$.info.address.town | N'Bristol' | N'Bristol' | N/a |
$.info."address" | NULL |
Error | Not a scalar value. Use JSON_QUERY instead. |
$.info.tags | NULL |
Error | Not a scalar value. Use JSON_QUERY instead. |
$.info.type[0] | NULL |
Error | Not an array. |
$.info.none | NULL |
Error | Property does not exist. |
The following example uses the values of the JSON properties town
and state
in query results. Since JSON_VALUE
preserves the collation of the source, the sort order of the results depends on the collation of the jsonInfo
column.
Note
(This example assumes that a table named Person.Person
contains a jsonInfo
column of JSON text, and that this column has the structure shown previously in the discussion of lax mode and strict mode. In the AdventureWorks
sample database, the Person
table does not in fact contain a jsonInfo
column.)
SELECT FirstName, LastName,
JSON_VALUE(jsonInfo,'$.info.address.town') AS Town
FROM Person.Person
WHERE JSON_VALUE(jsonInfo,'$.info.address.state') LIKE 'US%'
ORDER BY JSON_VALUE(jsonInfo,'$.info.address.town')
The following example extracts the value of the JSON property town
into a local variable.
DECLARE @jsonInfo NVARCHAR(MAX)
DECLARE @town NVARCHAR(32)
SET @jsonInfo=N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';
SET @town=JSON_VALUE(@jsonInfo,'$.info.address[0].town'); -- Paris
SET @town=JSON_VALUE(@jsonInfo,'$.info.address[1].town'); -- London
The following example creates computed columns based on the values of JSON properties.
CREATE TABLE dbo.Store
(
StoreID INT IDENTITY(1,1) NOT NULL,
Address VARCHAR(500),
jsonContent NVARCHAR(4000),
Longitude AS JSON_VALUE(jsonContent, '$.address[0].longitude'),
Latitude AS JSON_VALUE(jsonContent, '$.address[0].latitude')
)