使用内置函数 (SQL Server) 验证、查询和更改 JSON 数据Validate, Query, and Change JSON Data with Built-in Functions (SQL Server)

适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later

JSON 的内置支持包括本主题简要介绍的下列内置函数。The built-in support for JSON includes the following built-in functions described briefly in this topic.

  • ISJSON 测试字符串是否包含有效 JSON。ISJSON tests whether a string contains valid JSON.

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

  • JSON_QUERY 从 JSON 字符串中提取对象或数组。JSON_QUERY extracts an object or an array from a JSON string.

  • JSON_MODIFY 更新 JSON 字符串中属性的值,并返回已更新的 JSON 字符串。JSON_MODIFY updates the value of a property in a JSON string and returns the updated JSON string.

此页上的示例 JSON 文本JSON text for the examples on this page

此页上的示例使用与以下示例中所示内容类似的 JSON 文本:The examples on this page use the JSON text similar to the content shown in the following example:

{
  "id": "WakefieldFamily",
  "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
  ],
  "children": [
      {
        "familyName": "Merriam",
        "givenName": "Jesse",
        "gender": "female",
        "grade": 1,
        "pets": [
            { "givenName": "Goofy" },
            { "givenName": "Shadow" }
        ]
      },
      { 
        "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 8 }
  ],
  "address": { "state": "NY", "county": "Manhattan", "city": "NY" },
  "creationDate": 1431620462,
  "isRegistered": false
}

此 JSON 文档包含嵌套的复杂元素,存储在下面的示例表中:This JSON document, which contains nested complex elements, is stored in the following sample table:

CREATE TABLE Families (
   id int identity constraint PK_JSON_ID primary key,
   doc nvarchar(max)
)

使用 ISJSON 函数验证 JSON 文本Validate JSON text by using the ISJSON function

ISJSON 函数测试字符串是否包含有效 JSON。The ISJSON function tests whether a string contains valid JSON.

下面的示例将返回 JSON 列包含有效 JSON 文本的行。The following example returns rows in which the JSON column contains valid JSON text. 请注意,如果没有显式 JSON 约束,则可在 NVARCHAR 列中输入任意文本:Note that without explicit JSON constraint, you can enter any text in the NVARCHAR column:

SELECT *
FROM Families
WHERE ISJSON(doc) > 0 

有关详细信息,请参阅 ISJSON (Transact-SQL)For more info, see ISJSON (Transact-SQL).

使用 JSON_VALUE 函数从 JSON 文本中提取值Extract a value from JSON text by using the JSON_VALUE function

JSON_VALUE 函数从 JSON 字符串中提取标量值。The JSON_VALUE function extracts a scalar value from a JSON string. 下面的查询将返回其中 id JSON 字段与值 AndersenFamily 一致的文档,按 citystate JSON 字段排序:The following query will return the documents where the id JSON field matches the value AndersenFamily, ordered by city and state JSON fields:

SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
       JSON_VALUE(f.doc, '$.address.city') AS City,
       JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC, JSON_VALUE(f.doc, '$.address.state') ASC

此查询结果显示在下表中:The results of this query are shown in the following table:

名称Name 城市City County
AndersenFamilyAndersenFamily NYNY 曼哈顿Manhattan

有关详细信息,请参阅 JSON_VALUE (Transact-SQL)For more info, see JSON_VALUE (Transact-SQL).

使用 JSON_QUERY 函数从 JSON 文本中提取对象或数组Extract an object or an array from JSON text by using the JSON_QUERY function

JSON_QUERY 函数从 JSON 字符串中提取对象或数组。The JSON_QUERY function extracts an object or an array from a JSON string. 下面的示例演示了如何在查询结果中返回 JSON 片段。The following example shows how to return a JSON fragment in query results.

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
       JSON_QUERY(f.doc, '$.parents') AS Parents,
       JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'

此查询结果显示在下表中:The results of this query are shown in the following table:

地址Address 父项Parents Parent0Parent0
{ "state":"NY", "county":"Manhattan", "city":"NY" }{ "state": "NY", "county": "Manhattan", "city": "NY" } [{ "familyName":"Wakefield", "givenName":"Robin" }, {"familyName":"Miller", "givenName":"Ben" } ][{ "familyName": "Wakefield", "givenName": "Robin" }, {"familyName": "Miller", "givenName": "Ben" } ] { "familyName":"Wakefield", "givenName":"Robin" }{ "familyName": "Wakefield", "givenName": "Robin" }

有关详细信息,请参阅 JSON_QUERY (Transact-SQL)For more info, see JSON_QUERY (Transact-SQL).

分析嵌套式 JSON 集合Parse nested JSON collections

通过 OPENJSON 函数,可将 JSON 子数组转换为行集,然后将其与父元素联接在一起。OPENJSON function enables you to transform JSON sub-array into the rowset and then join it with the parent element. 例如,可返回所有家庭文档,并将其与存储为内部 JSON 数组的 children 对象“联接”起来:As an example, you can return all family documents, and "join" them with their children objects that are stored as an inner JSON array:

SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
       JSON_VALUE(f.doc, '$.address.city') AS City,
       c.givenName, c.grade
FROM Families f
        CROSS APPLY OPENJSON(f.doc, '$.children')
            WITH(grade int, givenName nvarchar(100))  c

此查询结果显示在下表中:The results of this query are shown in the following table:

名称Name 城市City givenNamegivenName 年级grade
AndersenFamilyAndersenFamily NYNY JesseJesse 11
AndersenFamilyAndersenFamily NYNY LisaLisa 88

最终我们得到两个行,因为一个父行与通过分析子级子数组的两个元素产生的两个子行联接在一起。We are getting two rows as a result because one parent row is joined with two child rows produced by parsing two elements of the children subarray. OPENJSON 函数分析 doc 列中的 children 片段,并返回每个元素中的 gradegivenName 作为一个行集。OPENJSON function parses children fragment from the doc column and returns grade and givenName from each element as a set of rows. 此行集可以与父文档进行联接。This rowset can be joined with the parent document.

查询嵌套式分层 JSON 子数组Query nested hierarchical JSON sub-arrays

可应用多个 CROSS APPLY OPENJSON 调用以查询嵌套式 JSON 结构。You can apply multiple CROSS APPLY OPENJSON calls in order to query nested JSON structures. 本示例中使用的 JSON 文档具有名为 children 的嵌套数组,其中每个子级都有 pets 的嵌套数组。The JSON document used in this example has a nested array called children, where each child has nested array of pets. 下面的查询将分析每个文档中的子级,将每个数组对象作为行返回,然后分析 pets 数组:The following query will parse children from each document, return each array object as row, and then parse pets array:

SELECT  familyName,
    c.givenName AS childGivenName,
    c.firstName AS childFirstName,
    p.givenName AS petName 
FROM Families f 
    CROSS APPLY OPENJSON(f.doc) 
        WITH (familyName nvarchar(100), children nvarchar(max) AS JSON)
        CROSS APPLY OPENJSON(children) 
        WITH (givenName nvarchar(100), firstName nvarchar(100), pets nvarchar(max) AS JSON) as c
            OUTER APPLY OPENJSON (pets)
            WITH (givenName nvarchar(100))  as p

首次进行 OPENJSON 调用将使用 AS JSON 子句返回 children 数组的片段。The first OPENJSON call will return fragment of children array using AS JSON clause. 此数组片段将提供给第二个 OPENJSON 函数,该函数将返回每个小孩的 firstNamegivenName 以及pets 数组。This array fragment will be provided to the second OPENJSON function that will return givenName, firstName of each child, as well as the array of pets. pets 的数组将提供给第三个 OPENJSON 函数,该函数将返回宠物的 givenNameThe array of pets will be provided to the third OPENJSON function that will return the givenName of the pet. 此查询结果显示在下表中:The results of this query are shown in the following table:

familyNamefamilyName childGivenNamechildGivenName childFirstNamechildFirstName petNamepetName
AndersenFamilyAndersenFamily JesseJesse MerriamMerriam GoofyGoofy
AndersenFamilyAndersenFamily JesseJesse MerriamMerriam ShadowShadow
AndersenFamilyAndersenFamily LisaLisa MillerMiller NULL

根文档与两个 children 行进行了联接,这两个行由生成两个行(或元组)的首次 OPENJSON(children) 调用返回。The root document is joined with two children rows returned by first OPENJSON(children) call making two rows (or tuples). 然后,使用 OUTER APPLY 运算符将每行与由 OPENJSON(pets) 生成的新行进行联接。Then each row is joined with the new rows generated by OPENJSON(pets) using OUTER APPLY operator. Jesse 有两只宠物,因此 (AndersenFamily, Jesse, Merriam) 与为 Goofy 和 Shadow 生成的两行进行了联接。Jesse has two pets, so (AndersenFamily, Jesse, Merriam) is joined with two rows generated for Goofy and Shadow. Lisa 没有宠物,因此 OPENJSON(pets) 没有为该元组返回的任何行。Lisa doesn't have the pets, so there are no rows returned by OPENJSON(pets) for this tuple. 但是,由于我们使用的是 OUTER APPLY,因此这列的结果是 NULLHowever, since we are using OUTER APPLY we are getting NULL in the column. 如果我们使用 CROSS APPLY 而不是 OUTER APPLY,则不会在结果中返回 Lisa,因为没有可以与该元组联接的任何宠物行。If we put CROSS APPLY instead of OUTER APPLY, Lisa would not be returned in the result because there are no pets rows that could be joined with this tuple.

对比 JSON_VALUE 与 JSON_QUERYCompare JSON_VALUE and JSON_QUERY

JSON_VALUEJSON_QUERY 之间的主要区别在于 JSON_VALUE 返回标量值,而 JSON_QUERY 返回数组或对象。The key difference between JSON_VALUE and JSON_QUERY is that JSON_VALUE returns a scalar value, while JSON_QUERY returns an object or an array.

请参考以下示例 JSON 文本。Consider the following sample JSON text.

{
    "a": "[1,2]",
    "b": [1, 2],
    "c": "hi"
}  

在此示例 JSON 文本中,数据成员“a”和“c”是字符串值,而数据成员“b”是数组。In this sample JSON text, data members "a" and "c" are string values, while data member "b" is an array. JSON_VALUEJSON_QUERY 返回以下结果:JSON_VALUE and JSON_QUERY return the following results:

路径Path JSON_VALUE 返回JSON_VALUE returns JSON_QUERY 返回JSON_QUERY returns
$ NULL 或错误NULL or error { "a": "[1,2]", "b": [1,2], "c":"hi"}
$.a$.a [1,2][1,2] NULL 或错误NULL or error
$.b$.b NULL 或错误NULL or error [1,2][1,2]
$.b[0]$.b[0] 11 NULL 或错误NULL or error
$.c$.c hihi NULL 或错误NULL or error

使用 AdventureWorks 示例数据库测试 JSON_VALUE 和 JSON_QUERYTest JSON_VALUE and JSON_QUERY with the AdventureWorks sample database

通过使用 AdventureWorks 示例数据库运行以下示例,对本主题中所述的内置函数进行测试。Test the built-in functions described in this topic by running the following examples with the AdventureWorks sample database. 有关何处获取 AdventureWorks 的信息,以及如何通过运行脚本添加用于测试的 JSON 数据,请参阅测试驱动的内置 JSON 支持For info about where to get AdventureWorks, and about how to add JSON data for testing by running a script, see Test drive built-in JSON support.

在下面的示例中,SalesOrder_json 表中的 Info 列包含了 JSON 文本。In the following examples, the Info column in the SalesOrder_json table contains JSON text.

示例 1 - 返回标准列和 JSON 数据Example 1 - Return both standard columns and JSON data

下面的查询返回将返回标准关系列以及 JSON 列的值。The following query returns values from both standard relational columns and from a JSON column.

SELECT SalesOrderNumber, OrderDate, Status, ShipDate, Status, AccountNumber, TotalDue,
 JSON_QUERY(Info,'$.ShippingInfo') ShippingInfo,
 JSON_QUERY(Info,'$.BillingInfo') BillingInfo,
 JSON_VALUE(Info,'$.SalesPerson.Name') SalesPerson,
 JSON_VALUE(Info,'$.ShippingInfo.City') City,
 JSON_VALUE(Info,'$.Customer.Name') Customer,
 JSON_QUERY(OrderItems,'$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0

示例 2 - 聚合和筛选 JSON 值Example 2- Aggregate and filter JSON values

下面的查询将按客户名称(存储在 JSON 中)和状态(存储在普通列中)来汇总小计。The following query aggregates subtotals by customer name (stored in JSON) and status (stored in an ordinary column). 然后将按市/县(存储在 JSON 中)和 OrderDate(存储在普通列中)来筛选结果。Then it filters the results by city (stored in JSON) and OrderDate (stored in an ordinary column).

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid=3;

SET @city=N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer, Status, SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID=@territoryid
 AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
 AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'), Status
HAVING SUM(SubTotal)>1000

使用 JSON_MODIFY 函数更新 JSON 文本中的属性值Update property values in JSON text by using the JSON_MODIFY function

JSON_MODIFY 函数更新 JSON 字符串中属性的值,并返回已更新的 JSON 字符串。The JSON_MODIFY function updates the value of a property in a JSON string and returns the updated JSON string.

以下示例将更新包含 JSON 的变量中的 JSON 属性的值。The following example updates the value of a JSON property in a variable that contains JSON.

SET @info = JSON_MODIFY(@jsonInfo, "$.info.address[0].town", 'London')    

有关详细信息,请参阅 JSON_MODIFY (Transact-SQL)For more info, see JSON_MODIFY (Transact-SQL).

详细了解 SQL Server 和 Azure SQL 数据库中的 JSONLearn more about JSON in SQL Server and Azure SQL Database

Microsoft 视频Microsoft videos

有关 SQL Server 和 Azure SQL 数据库中内置 JSON 支持的视频介绍,请观看以下视频:For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

另请参阅See Also

ISJSON (Transact-SQL) ISJSON (Transact-SQL)
JSON_VALUE (Transact-SQL) JSON_VALUE (Transact-SQL)
JSON_QUERY (Transact-SQL) JSON_QUERY (Transact-SQL)
JSON_MODIFY (Transact-SQL) JSON_MODIFY (Transact-SQL)
JSON 路径表达式 (SQL Server)JSON Path Expressions (SQL Server)