JSON_VALUE (Transact-SQL)JSON_VALUE (Transact-SQL)

适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

从 JSON 字符串中提取标量值。Extracts a scalar value from a JSON string.

若要从 JSON 字符串而不是标量值中提取对象或数组,请参阅 JSON_QUERY (Transact-SQL)To extract an object or an array from a JSON string instead of a scalar value, see JSON_QUERY (Transact-SQL). 有关 JSON_VALUE 和 JSON_QUERY 之间差异的信息,请参阅比较 JSON_VALUE 和 JSON_QUERYFor info about the differences between JSON_VALUE and JSON_QUERY, see Compare JSON_VALUE and JSON_QUERY.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

JSON_VALUE ( expression , path )  

参数Arguments

expressionexpression
一个表达式。An expression. 通常是包含 JSON 文本的变量或列的名称。Typically the name of a variable or a column that contains JSON text.

如果 JSON_VALUE 在找到由 path 标识的值之前,找到在 expression 中无效的 JSON,则函数会返回错误。If JSON_VALUE finds JSON that is not valid in expression before it finds the value identified by path, the function returns an error. 如果 JSON_VALUE 找不到路径标识的值,则它将扫描整个文本,如果在表达式中的任何位置发现无效的 JSON,则返回错误。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.

路径path
指定要提取属性的 JSON 路径。A JSON path that specifies the property to extract. 有关详细信息,请参阅 JSON 路径表达式 (SQL Server)For more info, see JSON Path Expressions (SQL Server).

SQL Server 2017 (14.x)SQL Server 2017 (14.x)Azure SQL 数据库Azure SQL Database 中,可提供变量作为 path 的值。In SQL Server 2017 (14.x)SQL Server 2017 (14.x) and in Azure SQL 数据库Azure SQL Database, you can provide a variable as the value of path.

如果 path 格式无效,则 JSON_VALUE 返回错误。If the format of path isn't valid, JSON_VALUE returns an error .

返回值Return value

返回 nvarchar(4000) 类型的单个文本值。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.

如果值大于 4000 个字符:If the value is greater than 4000 characters:

  • 在宽松模式下,JSON_VALUE 返回 NULL。In lax mode, JSON_VALUE returns null.

  • 在严格模式下,JSON_VALUE 返回错误。In strict mode, JSON_VALUE returns an error.

如果必须返回大于 4000 个字符的标量值,请使用 OPENJSON 而不是 JSON_VALUE。If you have to return scalar values greater than 4000 characters, use OPENJSON instead of JSON_VALUE. 有关详细信息,请参阅 OPENJSON (Transact-SQL)For more info, see OPENJSON (Transact-SQL).

备注Remarks

宽松模式和严格模式Lax mode and strict mode

请参考以下 JSON 文本: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"  
 }'  

下表对宽松模式和严格模式下 JSON_VALUE 的行为进行了比较。The following table compares the behavior of JSON_VALUE in lax mode and in strict mode. 有关可选路径模式规范(宽松或严格)的详细信息,请参阅 JSON 路径表达式 (SQL Server)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
$ NullNULL 错误Error 不是标量值。Not a scalar value.

改用 JSON_QUERY。Use JSON_QUERY instead.
$.info.type$.info.type N'1'N'1' N'1'N'1' 不适用N/a
$.info.address.town$.info.address.town N'Bristol'N'Bristol' N'Bristol'N'Bristol' 不适用N/a
$.info."address"$.info."address" NullNULL 错误Error 不是标量值。Not a scalar value.

改用 JSON_QUERY。Use JSON_QUERY instead.
$.info.tags$.info.tags NullNULL 错误Error 不是标量值。Not a scalar value.

改用 JSON_QUERY。Use JSON_QUERY instead.
$.info.type[0]$.info.type[0] NullNULL 错误Error 不是数组。Not an array.
$.info.none$.info.none NullNULL 错误Error 属性不存在。Property does not exist.
       

示例Examples

示例 1Example 1

以下示例在查询结果中使用 JSON 属性 townstate 的值。The following example uses the values of the JSON properties town and state in query results. 由于 JSON_VALUE 保留了源的排序规则,因此结果的排序顺序取决于 jsonInfo 列的排序规则。Since JSON_VALUE preserves the collation of the source, the sort order of the results depends on the collation of the jsonInfo column.

备注

(此示例假定名为 Person.Person 的表包含 JSON 文本的 jsonInfo 列,且此列具有先前宽松模式和严格模式讨论中所示的结构。(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. 在 AdventureWorks 示例数据库中,Person 表实际上不包含 jsonInfo 列。)In the AdventureWorks sample database, the Person table does not in fact contain a jsonInfo column.)

SELECT FirstName, LastName,
 JSON_VALUE(jsonInfo,'$.info.address[0].town') AS Town
FROM Person.Person
WHERE JSON_VALUE(jsonInfo,'$.info.address[0].state') LIKE 'US%'
ORDER BY JSON_VALUE(jsonInfo,'$.info.address[0].town')

示例 2Example 2

下面的示例将 JSON 属性 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

示例 3Example 3

下面的示例基于 JSON 属性的值创建计算列。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')
 )

另请参阅See also

JSON 路径表达式 (SQL Server) JSON Path Expressions (SQL Server)
JSON 数据 (SQL Server)JSON Data (SQL Server)