Azure SQL 数据库和 Azure SQL 托管实例中的 JSON 功能入门Getting started with JSON features in Azure SQL Database and Azure SQL Managed Instance

07/13/2020

适用于： Azure SQL 数据库 Azure SQL 托管实例

使用 Azure SQL 数据库和 Azure SQL 托管实例可以分析和查询以 JavaScript 对象表示法 (JSON) 格式表示的数据，并将关系数据导出为 JSON 文本。Azure SQL Database and Azure SQL Managed Instance let you parse and query data represented in JavaScript Object Notation (JSON) format, and export your relational data as JSON text. 以下 JSON 方案可用：The following JSON scenarios are available:

使用 FOR JSON 子句将关系数据设置为 JSON 格式。Formatting relational data in JSON format using FOR JSON clause.
处理 JSON 数据Working with JSON data
使用 JSON 标量函数查询 JSON 数据。Querying JSON data using JSON scalar functions.
使用 OPENJSON 函数将 JSON 转换为表格格式。Transforming JSON into tabular format using OPENJSON function.

将关系数据设置为 JSON 格式Formatting relational data in JSON format

如果 Web 服务从数据库层提取数据并以 JSON 格式提供响应，或者客户端 JavaScript 框架或库接受 JSON 格式的数据，则可以直接在 SQL 查询中将数据库内容格式化为 JSON。If you have a web service that takes data from the database layer and provides a response in JSON format, or client-side JavaScript frameworks or libraries that accept data formatted as JSON, you can format your database content as JSON directly in a SQL query. 不再需要编写应用程序代码将 Azure SQL 数据库或 Azure SQL 托管实例中的结果格式化为 JSON，也不再需要包含一些 JSON 序列化库来转换表格查询结果，并将对象序列化为 JSON 格式。You no longer have to write application code that formats results from Azure SQL Database or Azure SQL Managed Instance as JSON, or include some JSON serialization library to convert tabular query results and then serialize objects to JSON format. 可以使用 FOR JSON 子句将 SQL 查询结果格式化为 JSON，并直接在应用程序中使用这种格式。Instead, you can use the FOR JSON clause to format SQL query results as JSON and use it directly in your application.

以下示例使用 FOR JSON 子句，将 Sales.Customer 表中的行格式化为 JSON：In the following example, rows from the Sales.Customer table are formatted as JSON by using the FOR JSON clause:

select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH

FOR JSON PATH 子句将查询结果格式化为 JSON 文本。The FOR JSON PATH clause formats the results of the query as JSON text. 列名用作键，单元格值以 JSON 值的形式生成：Column names are used as keys, while the cell values are generated as JSON values:

[
{"CustomerName":"Eric Torres","PhoneNumber":"(307) 555-0100","FaxNumber":"(307) 555-0101"},
{"CustomerName":"Cosmina Vlad","PhoneNumber":"(505) 555-0100","FaxNumber":"(505) 555-0101"},
{"CustomerName":"Bala Dixit","PhoneNumber":"(209) 555-0100","FaxNumber":"(209) 555-0101"}
]

结果集已格式化为 JSON 数组格式，其中每行已格式化为单独的 JSON 对象。The result set is formatted as a JSON array where each row is formatted as a separate JSON object.

PATH 表示可以在列别名中使用点表示法自定义 JSON 结果的输出格式。PATH indicates that you can customize the output format of your JSON result by using dot notation in column aliases. 以下查询更改输出 JSON 格式中“CustomerName”键的名称，然后将电话和传真号码放入“Contact”子对象：The following query changes the name of the "CustomerName" key in the output JSON format, and puts phone and fax numbers in the "Contact" sub-object:

select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

此查询的输出如下所示：The output of this query looks like this:

{
    "Name":"Nada Jovanovic",
    "Contact":{
           "Phone":"(215) 555-0100",
           "Fax":"(215) 555-0101"
    }
}

在本示例中，通过指定 WITHOUT_ARRAY_WRAPPER 选项返回了单个 JSON 对象而不是数组。In this example, we returned a single JSON object instead of an array by specifying the WITHOUT_ARRAY_WRAPPER option. 如果确定要返回单个对象作为查询结果，可以使用此选项。You can use this option if you know that you are returning a single object as a result of query.

FOR JSON 子句的主要作用是从数据库返回格式化为嵌套 JSON 对象或数组的复杂分层数据。The main value of the FOR JSON clause is that it lets you return complex hierarchical data from your database formatted as nested JSON objects or arrays. 以下示例展示了如何将属于 Customer 的 Orders 表中的行包含为嵌套的 Orders 数组：The following example shows how to include the rows from the Orders table that belong to the Customer as a nested array of Orders:

select CustomerName as Name, PhoneNumber as Phone, FaxNumber as Fax,
        Orders.OrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate
from Sales.Customers Customer
    join Sales.Orders Orders
        on Customer.CustomerID = Orders.CustomerID
where Customer.CustomerID = 931
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER

无需单独发送查询来获取 Customer 数据，再提取相关 Orders 列表，而可以通过一个查询来获取全部所需的数据，如以下示例输出中所示：Instead of sending separate queries to get Customer data and then to fetch a list of related Orders, you can get all the necessary data with a single query, as shown in the following sample output:

{
  "Name":"Nada Jovanovic",
  "Phone":"(215) 555-0100",
  "Fax":"(215) 555-0101",
  "Orders":[
    {"OrderID":382,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":395,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":1657,"OrderDate":"2013-01-31","ExpectedDeliveryDate":"2013-02-01"}
  ]
}

处理 JSON 数据Working with JSON data

如果没有严格结构化的数据、有复杂的子对象、数组或分层数据，或者数据结构随时间演变，则可以借助 JSON 格式来表示任何复杂数据结构。If you don't have strictly structured data, if you have complex sub-objects, arrays, or hierarchical data, or if your data structures evolve over time, the JSON format can help you to represent any complex data structure.

JSON 是一种文本格式，与其他任何字符串类型一样，可以在 Azure SQL 数据库和 Azure SQL 托管实例中使用。JSON is a textual format that can be used like any other string type in Azure SQL Database and Azure SQL Managed Instance. 可以将 JSON 数据作为标准 NVARCHAR 来发送或存储：You can send or store JSON data as a standard NVARCHAR:

CREATE TABLE Products (
  Id int identity primary key,
  Title nvarchar(200),
  Data nvarchar(max)
)
go
CREATE PROCEDURE InsertProduct(@title nvarchar(200), @json nvarchar(max))
AS BEGIN
    insert into Products(Title, Data)
    values(@title, @json)
END

本示例中的 JSON 数据是使用 NVARCHAR(MAX) 类型表示的。The JSON data used in this example is represented by using the NVARCHAR(MAX) type. 可以使用标准 Transact-SQL 语法将 JSON 插入此表，或将其用作存储过程的参数，如以下示例所示：JSON can be inserted into this table or provided as an argument of the stored procedure using standard Transact-SQL syntax as shown in the following example:

EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'

适用于 Azure SQL 数据库和 Azure SQL 托管实例中的字符串数据的任何客户端语言或库同样也适用于 JSON 数据。Any client-side language or library that works with string data in Azure SQL Database and Azure SQL Managed Instance will also work with JSON data. JSON 可存储在任何支持 NVARCHAR 类型的表中，例如内存优化表或者版本由系统控制的表。JSON can be stored in any table that supports the NVARCHAR type, such as a Memory-optimized table or a System-versioned table. JSON 不会在客户端代码或数据库层中引入任何约束。JSON does not introduce any constraint either in the client-side code or in the database layer.

查询 JSON 数据Querying JSON data

如果格式化为 JSON 的数据已存储在 Azure SQL 表中，可以通过 JSON 函数在任何 SQL 查询中使用此数据。If you have data formatted as JSON stored in Azure SQL tables, JSON functions let you use this data in any SQL query.

通过可在 Azure SQL 数据库和 Azure SQL 托管实例中使用的 JSON 函数，可将格式化为 JSON 的数据视为其他任何 SQL 数据类型。JSON functions that are available in Azure SQL Database and Azure SQL Managed Instance let you treat data formatted as JSON as any other SQL data type. 可以轻松地从 JSON 文本中提取值，并在任何查询中使用 JSON 数据：You can easily extract values from the JSON text, and use JSON data in any query:

select Id, Title, JSON_VALUE(Data, '$.Color'), JSON_QUERY(Data, '$.tags')
from Products
where JSON_VALUE(Data, '$.Color') = 'White'

update Products
set Data = JSON_MODIFY(Data, '$.Price', 60)
where Id = 1

JSON_VALUE 函数从 Data 列中存储的 JSON 文本提取值。The JSON_VALUE function extracts a value from JSON text stored in the Data column. 此函数使用类似于 JavaScript 的路径来引用要提取的 JSON 文本中的值。This function uses a JavaScript-like path to reference a value in JSON text to extract. 提取的值可以在 SQL 查询的任何部分中使用。The extracted value can be used in any part of SQL query.

JSON_QUERY 函数类似于 JSON_VALUE。The JSON_QUERY function is similar to JSON_VALUE. 不同于 JSON_VALUE，此函数提取复杂的子对象，例如放置在 JSON 文本中的数组或对象。Unlike JSON_VALUE, this function extracts complex sub-object such as arrays or objects that are placed in JSON text.

使用 JSON_MODIFY 函数可指定 JSON 文本中应更新的值的路径，以及用于覆盖旧值的新值。The JSON_MODIFY function lets you specify the path of the value in the JSON text that should be updated, as well as a new value that will overwrite the old one. 这样，便可以轻松更新 JSON 文本，而无需重新分析整个结构。This way you can easily update JSON text without reparsing the entire structure.

由于 JSON 以标准文本存储，因此无法保证存储在文本列中的值格式正确。Since JSON is stored in a standard text, there are no guarantees that the values stored in text columns are properly formatted. 可以使用标准的 Azure SQL 数据库检查约束和 ISJSON 函数，来验证 JSON 列中存储的文本是否格式正确：You can verify that text stored in JSON column is properly formatted by using standard Azure SQL Database check constraints and the ISJSON function:

ALTER TABLE Products
    ADD CONSTRAINT [Data should be formatted as JSON]
        CHECK (ISJSON(Data) > 0)

如果输入的文本是格式正确的 JSON，ISJSON 函数返回值 1。If the input text is properly formatted JSON, the ISJSON function returns the value 1. 在每次插入或更新 JSON 列时，此约束都会验证新文本值是否为格式正确的 JSON。On every insert or update of JSON column, this constraint will verify that new text value is not malformed JSON.

将 JSON 转换为表格格式Transforming JSON into tabular format

Azure SQL 数据库和 Azure SQL 托管实例还允许将 JSON 集合转换为表格格式，并加载或查询 JSON 数据。Azure SQL Database and Azure SQL Managed Instance also let you transform JSON collections into tabular format and load or query JSON data.

OPENJSON 是一个表值函数，可分析 JSON 文本、查找 JSON 对象数组、迭代数组的元素，并在输出结果中针对每个数组元素返回一行。OPENJSON is a table-value function that parses JSON text, locates an array of JSON objects, iterates through the elements of the array, and returns one row in the output result for each element of the array.

JSON 表格

在以上示例中，可以指定要在何处查找应打开的 JSON 数组（在 $.Orders 路径中）、应返回哪些列作为结果，以及要在何处查找将作为单元格返回的 JSON 值。In the example above, we can specify where to locate the JSON array that should be opened (in the $.Orders path), what columns should be returned as result, and where to find the JSON values that will be returned as cells.

可以将 @orders 变量中的 JSON 数组转换为行集、分析此结果集，或将行插入标准表中：We can transform a JSON array in the @orders variable into a set of rows, analyze this result set, or insert rows into a standard table:

CREATE PROCEDURE InsertOrders(@orders nvarchar(max))
AS BEGIN

    insert into Orders(Number, Date, Customer, Quantity)
    select Number, Date, Customer, Quantity
    FROM OPENJSON (@orders)
     WITH (
            Number varchar(200),
            Date datetime,
            Customer varchar(200),
            Quantity int
     )
END

可以分析采用 JSON 数组格式并作为参数提供给存储过程的订单集合，并将它插入 Orders 表。The collection of orders formatted as a JSON array and provided as a parameter to the stored procedure can be parsed and inserted into the Orders table.

后续步骤Next steps

要了解如何将 JSON 集成到应用程序中，请参阅以下资源：To learn how to integrate JSON into your application, check out these resources: