SQL Server 2016 JSON原生支持实例说明

栏目: SQL Server · 发布时间: 8年前

内容简介：Microsoft SQL Server 对于数据平台的开发者来说越来越友好。比如已经原生支持XML很多年了，在这个趋势下，如今也能在SQLServer2016中使用内置的JSON。尤其对于一些大数据很数据接口的解析环节来说这显得非常有价值。

背景

Microsoft SQL Server 对于数据平台的开发者来说越来越友好。比如已经原生支持XML很多年了，在这个趋势下，如今也能在SQLServer2016中使用内置的JSON。尤其对于一些大数据很数据接口的解析环节来说这显得非常有价值。与我们现在所做比如在 SQL 中使用CLR或者自定义的函数来解析JSON相比较，新的内置JSON会大大提高性能，同时优化了编程以及增删查改等方法。

那么是否意味着我们可以丢弃XML，然后开始使用JSON？当然不是，这取决于数据输出处理的目的。如果有一个外部的通过XML与外部交互数据的服务并且内外的架构是一致的，那么应该是使用XML数据类型以及原生的函数。如果是针对微型服务架构或者动态元数据和数据存储，那么久应该利用最新的JSON函数。

实例

当使用查询这些已经有固定架构的JSON的数据表时，使用“FOR JSON” 提示在你的T-SQL脚本后面，用这种方式以便于格式化输出。一下实例我使用了SQLServer 2016 Worldwide Importers sample database，可以在GitHub上直接下载下来（下载地址）。看一下视图Website.customers。我们查询一个数据并格式化输出JSON格式：

SELECT [CustomerID]
      ,[CustomerName]
      ,[CustomerCategoryName]
      ,[PrimaryContact]
      ,[AlternateContact]
      ,[PhoneNumber]
      ,[FaxNumber]
      ,[BuyingGroupName]
      ,[WebsiteURL]
      ,[DeliveryMethod]
      ,[CityName]
      
 ,DeliveryLocation.ToString() as DeliveryLocation
      ,[DeliveryRun]
      ,[RunPosition]
  FROM [WideWorldImporters].[Website].[Customers]
  WHERE CustomerID=1
  FOR JSON AUTO

SELECT [CustomerID]

,[CustomerName]

,[CustomerCategoryName]

,[PrimaryContact]

,[AlternateContact]

,[PhoneNumber]

,[FaxNumber]

,[BuyingGroupName]

,[WebsiteURL]

,[DeliveryMethod]

,[CityName]

,DeliveryLocation.ToString() as DeliveryLocation

,[DeliveryRun]

,[RunPosition]

FROM [WideWorldImporters].[Website].[Customers]

WHERE CustomerID=1

FOR JSON AUTO

请注意我们有一个地理数据类型列（DeliveryLocation），这需要引入两个重要的变通方案（标黄）:

首先，需要转换一个string字符，否则就会报错:

FOR JSON cannot serialize CLR objects. Cast CLR types explicitly into one of the supported types in FOR JSON queries.

其次，JSON采用键值对的语法因此必须指定一个别名来转换数据，如果失败会出现下面的错误:

Column expressions and data sources without names or aliases cannot be formatted as JSON text using FOR JSON clause. Add alias to the unnamed column or table.

确认了这些，改写的格式化输出如下:

[
    {
        "CustomerID": 1,
        "CustomerName": "Tailspin Toys (Head Office)",
        "CustomerCategoryName": "Novelty Shop",
        "PrimaryContact": "Waldemar Fisar",
        "AlternateContact": "Laimonis Berzins",
        "PhoneNumber": "(308) 555-0100",
        "FaxNumber": "(308) 555-0101",
        "BuyingGroupName": "Tailspin Toys",
        "WebsiteURL": "http://www.tailspintoys.com",
        "DeliveryMethod": "Delivery Van",
        "CityName": "Lisco",
        "DeliveryLocation": "POINT (-102.6201979 41.4972022)",
        "DeliveryRun": "",
        "RunPosition": ""
    }
]

[

{

"CustomerID": 1,

"CustomerName": "Tailspin Toys (Head Office)",

"CustomerCategoryName": "Novelty Shop",

"PrimaryContact": "Waldemar Fisar",

"AlternateContact": "Laimonis Berzins",

"PhoneNumber": "(308) 555-0100",

"FaxNumber": "(308) 555-0101",

"BuyingGroupName": "Tailspin Toys",

"WebsiteURL": "http://www.tailspintoys.com",

"DeliveryMethod": "Delivery Van",

"CityName": "Lisco",

"DeliveryLocation": "POINT (-102.6201979 41.4972022)",

"DeliveryRun": "",

"RunPosition": ""

}

]

当然也可以使用JSON作为输入型DML语句，例如INSERT/UPDATE/DELETE 语句中使用“OPENJSON”。因此可以在所有的数据操作上加入JSON提示。

如果不了解数据结构或者想让其更加灵活，那么可以将数据存储为一个JSON格式的字符类型，改列的类型可以使NVARCHAR 类型。Application.People 表中的CustomFields 列就是典型这种情况。可以用如下语句看一下表格格式这个列的内容:

declare json nvarchar(max)

SELECT @json=[CustomFields]
FROM [WideWorldImporters].[Application].[People]
where PersonID=8

select * from openjson(json)

declare json nvarchar(max)

SELECT @json=[CustomFields]

FROM [WideWorldImporters].[Application].[People]

where PersonID=8

select * from openjson(json)

结果集在表格结果中的显示:

SQL Server 2016 JSON原生支持实例说明

用另一种方式来查询这条记录，前提是需要知道在JSON数据结构和关键的名字，使用JSON_VALUE 和JSON_QUERY 函数:

  SELECT
       JSON_QUERY([CustomFields],'$.OtherLanguages') as OtherLanguages,
       JSON_VALUE([CustomFields],'$.HireDate') as HireDate,
       JSON_VALUE([CustomFields],'$.Title') as Title,
       JSON_VALUE([CustomFields],'$.PrimarySalesTerritory') as PrimarySalesTerritory,
       JSON_VALUE([CustomFields],'$.CommissionRate') as CommissionRate
  FROM [WideWorldImporters].[Application].[People]
  where PersonID=8

SELECT

JSON_QUERY([CustomFields],'$.OtherLanguages') as OtherLanguages,

JSON_VALUE([CustomFields],'$.HireDate') as HireDate,

JSON_VALUE([CustomFields],'$.Title') as Title,

JSON_VALUE([CustomFields],'$.PrimarySalesTerritory') as PrimarySalesTerritory,

JSON_VALUE([CustomFields],'$.CommissionRate') as CommissionRate

FROM [WideWorldImporters].[Application].[People]

where PersonID=8

在表格结果集中展示表格格式的结果:

SQL Server 2016 JSON原生支持实例说明

这个地方最关心就是查询条件和添加索引。设想一下我们打算去查询所有2011年以后雇佣的人，你可以运行下面的查询语句:

SELECT personID,fullName,JSON_VALUE(CustomFields,'$.HireDate') as hireDate
FROM [WideWorldImporters].[Application].[People]
where IsEmployee=1
and year(cast(JSON_VALUE(CustomFields,'$.HireDate') as date))>2011

SELECT personID,fullName,JSON_VALUE(CustomFields,'$.HireDate') as hireDate

FROM [WideWorldImporters].[Application].[People]

where IsEmployee=1

and year(cast(JSON_VALUE(CustomFields,'$.HireDate') as date))>2011

切记JSON_VALUE 返回一个单一的文本值（nvarchar(4000)）。需要转换返回值到一个时间字段中，然后分离年来筛选查询条件。实际执行计划如下:

SQL Server 2016 JSON原生支持实例说明

为了验证如何对JSON内容创建索引，需要创建一个计算列。为了举例说明，Application.People 表标记版本，并且加入计算列，当系统版本为ON的时候不支持。我们这里使用Sales.Invoices表，其中ReturnedDeliveryData 中插入json数据。接下来获取数据，感受一下:

SELECT TOP 100 [InvoiceID]
      ,[CustomerID]
      ,JSON_QUERY([ReturnedDeliveryData],'$.Events')
  FROM [WideWorldImporters].[Sales].[Invoices]

SELECT TOP 100 [InvoiceID]

,[CustomerID]

,JSON_QUERY([ReturnedDeliveryData],'$.Events')

FROM [WideWorldImporters].[Sales].[Invoices]

发现结果集第一个event都是“Ready for collection”:

SQL Server 2016 JSON原生支持实例说明

然后获取2016年3月的发票数据:

SELECT [InvoiceID]
      ,[CustomerID]
      ,CONVERT(datetime, CONVERT(varchar,JSON_VALUE([ReturnedDeliveryData],'$.Events[0].EventTime')),126)
  FROM [WideWorldImporters].[Sales].[Invoices]
  WHERE CONVERT(datetime, CONVERT(varchar,JSON_VALUE([ReturnedDeliveryData],'$.Events[0].EventTime')),126)
       BETWEEN '20160301' AND '20160331'

SELECT [InvoiceID]

,[CustomerID]

,CONVERT(datetime, CONVERT(varchar,JSON_VALUE([ReturnedDeliveryData],'$.Events[0].EventTime')),126)

FROM [WideWorldImporters].[Sales].[Invoices]

WHERE CONVERT(datetime, CONVERT(varchar,JSON_VALUE([ReturnedDeliveryData],'$.Events[0].EventTime')),126)

BETWEEN '20160301' AND '20160331'

实际执行计划如下:

SQL Server 2016 JSON原生支持实例说明

加入一个计算列叫做“ReadyDate”, 准备好集合表达式的结果:

ALTER TABLE [WideWorldImporters].[Sales].[Invoices]
ADD ReadyDate AS CONVERT(datetime, CONVERT(varchar,JSON_VALUE([ReturnedDeliveryData],'$.Events[0].EventTime')),126)

1 2	ALTER TABLE [WideWorldImporters].[Sales].[Invoices] ADD ReadyDate AS CONVERT(datetime, CONVERT(varchar,JSON_VALUE([ReturnedDeliveryData],'$.Events[0].EventTime')),126)

之后，重新执行查询，但是使用新的计算列作为条件:

SELECT [InvoiceID]
      ,[CustomerID]
      ,ReadyDate
  FROM [WideWorldImporters].[Sales].[Invoices]
  WHERE ReadyDate BETWEEN '20160301' AND '20160331'

SELECT [InvoiceID]

,[CustomerID]

,ReadyDate

FROM [WideWorldImporters].[Sales].[Invoices]

WHERE ReadyDate BETWEEN '20160301' AND '20160331'

执行计划是一样的，除了SSMS建议的缺失索引:

SQL Server 2016 JSON原生支持实例说明

因此，根据建议在计算列上建立索引来帮助查询，建立索引如下:

/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.272%.
*/
CREATE NONCLUSTERED INDEX IX_Invoices_ReadyDate
ON [Sales].[Invoices] ([ReadyDate])
INCLUDE ([InvoiceID],[CustomerID])
GO

The Query Processor estimates that implementing the following index could improve the query cost by 99.272%.

CREATE NONCLUSTERED INDEX IX_Invoices_ReadyDate

ON [Sales].[Invoices] ([ReadyDate])

INCLUDE ([InvoiceID],[CustomerID])

我们重新执行查询验证执行计划:

SQL Server 2016 JSON原生支持实例说明

有了索引之后，大大提升了性能，并且查询JSON的速度和表列是一样快的。

总结:

本篇通过对SQL2016 中的新增的内置JSON进行了简单介绍，主要有如下要点：

JSON能在SQLServer2016中高效的使用，但是JSON并不是原生数据类型;
如果使用JSON格式必须为输出结果是表达式的提供别名;
JSON_VALUE 和 JSON_QUERY 函数转移和获取Varchar格式的数据，因此必须将数据转译成你需要的类型。
在计算列的帮助下查询JSON可以使用索引进行优化。

原文出处： DB乐之者

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持码农网

查看所有标签

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍

Head First HTML and CSS

Elisabeth Robson、Eric Freeman / O'Reilly Media / 2012-9-8 / USD 39.99

Tired of reading HTML books that only make sense after you're an expert? Then it's about time you picked up Head First HTML and really learned HTML. You want to learn HTML so you can finally create th......一起来看看《Head First HTML and CSS》这本书的介绍吧!

码农工具

SQL Server 2016 JSON原生支持实例说明

背景

实例

总结:

Head First HTML and CSS

HTML 压缩/解压工具

JS 压缩/解压工具

URL 编码/解码