适用于:Azure SQL 数据库
Azure SQL 托管实例
使用 Azure SQL 数据库和 Azure SQL 托管实例可以分析和查询以 JavaScript 对象表示法 (JSON) 格式表示的数据,并将关系数据导出为 JSON 文本。 以下 JSON 方案可用:
- 使用 子句
FOR JSON
。 - 处理 JSON 数据
- 使用 JSON 标量函数查询 JSON 数据。
- 使用 函数
OPENJSON
。
如果 Web 服务从数据库层提取数据并以 JSON 格式提供响应,或者客户端 JavaScript 框架或库接受 JSON 格式的数据,则可以直接在 SQL 查询中将数据库内容格式化为 JSON。 你不再需要编写将结果格式化为 JSON 的应用程序代码,或包括一些 JSON 序列化库来转换表格查询结果,然后将对象序列化为 JSON 格式。 相反,可以使用 FOR JSON
子句将 SQL 查询结果格式化为 JSON,并直接在应用程序中使用它。
以下示例使用 FOR JSON 子句,将 Sales.Customer
表中的行格式化为 JSON:
select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH
子 FOR JSON PATH
句将查询的结果格式化为 JSON 文本。 列名用作键,单元格值以 JSON 值的形式生成:
[
{"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 对象。
PATH
指示可以使用列别名中的点表示法自定义 JSON 结果的输出格式。 以下查询更改输出 JSON 格式的密钥名称 CustomerName
,并将电话号码和传真号置于子对象中 Contact
:
select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
此查询的输出如下所示:
{
"Name":"Nada Jovanovic",
"Contact":{
"Phone":"(215) 555-0100",
"Fax":"(215) 555-0101"
}
}
在本示例中,通过指定 WITHOUT_ARRAY_WRAPPER 选项返回了单个 JSON 对象而不是数组。 如果确定要返回单个对象作为查询结果,可以使用此选项。
子句的主要 FOR JSON
值是,它允许从格式化为嵌套 JSON 对象或数组的数据库返回复杂的分层数据。 以下示例展示了如何将属于 Orders
的 Customer
表中的行包含为嵌套的 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
列表,如以下示例输出中所示:
{
"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 格式来表示任何复杂数据结构。
JSON 是一种文本格式,可以像其他任何字符串类型一样使用。 可以将 JSON 数据作为标准 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) 类型表示。 可以使用标准 Transact-SQL 语法将 JSON 插入此表,或将其用作存储过程的参数,如以下示例所示:
EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'
可以处理字符串数据的任何客户端语言或库同样也可以处理 JSON 数据。 JSON 可存储在任何支持 nvarchar 类型的表中,例如内存优化表或者版本由系统控制的表。 JSON 不会在客户端代码或数据库层中引入任何约束。
如果数据的格式设置为存储在表中的 JSON,JSON 函数允许在任何 SQL 查询中使用此数据。
JSON 函数允许将格式化为 JSON 的数据视为其他任何 SQL 数据类型。 可以轻松地从 JSON 文本中提取值,并在任何查询中使用 JSON 数据:
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
函数从存储在数据列中的 JSON 文本中提取值。 此函数使用类似于 JavaScript 的路径来引用要提取的 JSON 文本中的值。 提取的值可以在 SQL 查询的任何部分中使用。
该 JSON_QUERY
函数类似于 JSON_VALUE
. 与此函数不同 JSON_VALUE
,此函数提取复杂的子对象,例如放置在 JSON 文本中的数组或对象。
该 JSON_MODIFY
函数允许你在应更新的 JSON 文本中指定值的路径,以及将覆盖旧值的新值。 这样,便可以轻松更新 JSON 文本,而无需重新分析整个结构。
由于 JSON 以标准文本存储,因此无法保证存储在文本列中的值格式正确。 可以使用标准的检查约束和 ISJSON
函数,来验证 JSON 列中存储的文本是否格式正确:
ALTER TABLE Products
ADD CONSTRAINT [Data should be formatted as JSON]
CHECK (ISJSON(Data) > 0)
如果输入文本格式正确的 JSON,函数 ISJSON
将返回值 1
。 在每次插入或更新 JSON 列时,此约束都会验证新文本值是否为格式正确的 JSON。
可以将 JSON 集合转换为表格格式,然后加载或查询 JSON 数据。
OPENJSON
是一个表值 T-SQL 函数,用于分析 JSON 文本、查找 JSON 对象的数组、循环访问数组的元素,并在数组的每个元素的输出结果中返回一行。
在此示例中,我们可以指定在何处找到应打开的 JSON 数组(在 $.Orders
路径中)、应返回哪些列,以及在何处查找将作为单元格返回的 JSON 值。
可以将 @orders
变量中的 JSON 数组转换为行集、分析此结果集,或将行插入标准表中:
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 表。