JSON 功能入门

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

使用 Azure SQL 数据库和 Azure SQL 托管实例可以分析和查询以 JavaScript 对象表示法 (JSON) 格式表示的数据,并将关系数据导出为 JSON 文本。 以下 JSON 方案可用:

将关系数据格式化为 JSON 格式

如果 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 对象或数组的数据库返回复杂的分层数据。 以下示例展示了如何将属于 OrdersCustomer 表中的行包含为嵌套的 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 是一种文本格式,可以像其他任何字符串类型一样使用。 可以将 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,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 集合转换为表格格式,然后加载或查询 JSON 数据。

OPENJSON 是一个表值 T-SQL 函数,用于分析 JSON 文本、查找 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 表。