SQL Server 2016 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

  

 

求小心我们发出一个地理数据类型列(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": ""
    }
]

  

 

当然也得以以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)

  

 

结果集在表格结果丁的示:

图片 1

 

故而任何一样种植方法来询问这漫长记下,前提是用懂得在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

  

 

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

图片 2

 

以此地方太关怀就是查询条件与添加索引。设想一下我们打算去查询有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))。需要更换返回值到一个日子字段中,然后分别年来罗查询条件。实际施行计划如下:

图片 3

 

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

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

  

 

意识结果集第一个event都是“Ready for collection”:

图片 4

 

然后拿走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'

  

骨子里履行计划如下:

图片 5

 

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

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'

  

 

推行计划是一律的,除了SSMS建议的缺失索引:

图片 6

 

从而,根据建议于计算列上成立索引来帮助查询,建立目录如下:

/*
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

  

 

咱们更履行查询求证执行计划:

图片 7

 

发生矣目录之后,大大提升了性能,并且询问JSON的速与表列是同快的。

总结:

本篇通过对SQL2016 中的新增的停放JSON进行了概括介绍,主要发生如下要点:

 

  • JSON能于SQLServer2016中迅速之运,但是JSON并无是原生数据类型;
  • 假定应用JSON格式必须为出口结果是表达式的供别名;
  • JSON_VALUE 和 JSON_QUERY 
    函数转移和获得Varchar格式的数码,因此要将数据转译成你待的种类。
  • 在盘算列的助下询问JSON可以利用索引进行优化。

相关文章