SQL Server 2016 JSON原生援救实例证实

如今也能在SQLServer2016中使用内置的JSON,那么应该是使用XML数据类型以及原生的函数,如今也能在SQLServer2016中使用内置的JSON,那么应该是使用XML数据类型以及原生的函数,SQL Server 2005 开始支持 XML 数据类型,提供原生的 XML数据类型、XML,SQL Server 2005 开始支持 XML 数据类型,提供原生的 XML数据类型、XML

总结:

本篇通过对SQL贰零1四 中的新增的嵌入JSON举行了简便易行介绍,首要有如下要点:

 

  • JSON能在SQLServer贰零一四中飞快的接纳,不过JSON并不是原生数据类型;
  • 假使运用JSON格式必须为出口结果是表达式的提供别称;
  • JSON_VALUE 和 JSON_QUE哈弗Y 
    函数转移和得到Varchar格式的多寡,由此必须将数据转译成你须求的类型。
  • 在统计列的增加帮衬下询问JSON能够采纳索引实行优化。

实例

    当使用查询那个曾经有固定架构的JSON的数量表时,使用“FOR
JSON”
提醒在你的T-SQL脚本前边,用这种方法以便于格式化输出。一下实例笔者利用了SQLServer
201陆 Worldwide Importers sample
database,可以在GitHub上一贯下载下来(下载地址)。看一下视图Website.customers。笔者们询问2个数据并格式化输出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

  

 

请留心大家有3个地理数据类型列(DeliveryLocation),那亟需引入七个十分重要的转变方案(标黄):

第一,必要更换二个string字符,不然就会报错:

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

附带,JSON采纳键值对的语法因而必须钦赐2个小名来转换数据,要是战败会产出上边包车型地铁谬误:

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语句,例如INSE奔驰G级T/UPDATE/DELETE
语句中接纳“OPENJSON”。由此得以在拥有的数量操作上进入JSON提醒。

假如不领悟数据结构大概想让其更为灵活,那么能够将数据存款和储蓄为一个JSON格式的字符类型,改列的品种能够使NVA牧马人CHAHaval类型。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

 

其壹地点最关怀就是查询条件和添加索引。设想一下我们打算去询问全体201一年从此雇佣的人,你能够运维上边包车型客车查询语句:

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(四千))。要求转移再次回到值到一个时光字段中,然后分别年来筛选查询条件。实际执行安插如下:

图片 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

 

下一场拿走二〇一四年7月的发票数量:

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的快慢和表列是如出壹辙快的。

SQL Server201六 原生匡助JSON

 

SQL Server 200伍 开首帮助 XML 数据类型,提供原生的 XML数据类型、XML
索引以及种种管理 XML 或输出 XML 格式的函数。

在 SQL Server 时隔 四个至关心爱慕要版本其后,终于在 Microsoft Ignite 201五大会上正式发布,新一代的 SQL Server 201陆正式帮衬未来最风靡的数据交换格式— JSON(JavaScript Object Notation)。

 

SQL Server 二〇一四对JSON的帮忙并不是充实贰个JSON数据类型,而是提供贰个更轻便的框架,扶助用户在数据Curry处理JSON格式数据。

用户不需求更变现有的表结构,因为SQL Server使用NVAEnclaveCHA途观数据类型来储存JSON文件,并且跟现有技术并行相配,比如全文字笔迹检查评定索、列存款和储蓄索引、in-memory
OLTP,应用程序不必要做别的修改

 

不需求接纳JSON.Net那类工具分析和拍卖JSON数据,利用SQL
Server内置函数就足以处理JSON数据,轻松将查询结构输出为JSON格式,只怕搜索JSON文件内容。

 


使用 JSON AUTO 输出JSON 格式

要将select语句的结果以JSON输出,最简便易行的不二等秘书籍是在背后加上 FO昂Cora JSON AUTO

测试版本

Microsoft SQL Server 2016 (CTP2.2) - 13.0.407.1 (X64)   Jul 22 2015 21:19:11   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

SELECT * FROM [dbo].[Client] 
GO

SELECT * FROM [dbo].[Client] FOR JSON AUTO
GO

 

图片 8

 

咱俩得以把每列中呈现的最大字符数 设置为819二

图片 9

图片 10

 


加上Root Key

设若想为FO索罗德 JSON 加上Root Key,能够运用ROOT选项来钦命 Root Key 名称

SELECT * FROM [dbo].[Client] FOR JSON AUTO,ROOT('SUSU')
GO

图片 11

 


使用JSON PATH 输出JSON格式

当想要自定义输出JSON格式结构的时候,必须用JSON PATH描述,若SELECT
的字段名称一致,必须用别有名的模特式来重新命名字段名那样才得以接二连三查询

其它,假若字段的暗中认可值为NULL,那么输出JSON时,JSON会忽略null的只。假若要体现null值,能够增进INCLUDE_NULL_VALUES
选项(同样适用于JSON AUTO字句)

select * from  [dbo].[Client]

--FOR JSON PATH
SELECT * FROM [dbo].[Client] WHERE ClientID =2
FOR JSON PATH

[{"ClientID":2,"Firstname":"Peter","Lastname":"Nielsen","Birthdate":"1998-05-19T00:00:00","Email":"Peter@126.com","PhoneNumber":"+86-16326269674","Birthplace":"Stockholm","SocialSecurityNumber":"1901531234"}]

--FOR JSON PATH
SELECT * FROM [dbo].[Client] WHERE ClientID =4
FOR JSON PATH,INCLUDE_NULL_VALUES

[{"ClientID":4,"Firstname":"kade","Lastname":null,"Birthdate":"1980-01-06T00:00:00","Email":"Lotte@SOHU.com","PhoneNumber":"+86-16326269674","Birthplace":"Aalborg","SocialSecurityNumber":"1061234"}]

图片 12

 

 

越多关于JSON的作用

此时此刻SQL Server 201陆 CTP二对此JSON的意义支撑照旧比较有限,例如内置处理JSON格式化的函数,

ISJSON(判断是或不是是JSON格式)、JSON_VALUE(分析JSON文件并领取出值)
、OPENJSON(将JSON文件转换为1般数据表)

这几个功能要等到CTP三才能陆续推出

 

更加多SQL Server201陆好用的作用,敬请期待o(∩_∩)o 

SQL Server201陆 原生扶助JSON

 

SQL Server 200五 早先帮助 XML 数据类型,提供原生的 XML数据类型、XML
索引以及种种管理 XML 或输出 XML 格式的函数。

在 SQL Server 时隔 5个主要版本然后,终于在 Microsoft Ignite 20一中国共产党第五次全国代表大会会上专业揭橥,新一代的 SQL Server 201陆正式协助未来最风靡的数据交流格式— JSON(JavaScript Object Notation)。

 

SQL Server 贰零壹4对JSON的支撑并不是扩展叁个JSON数据类型,而是提供1个更轻便的框架,帮忙用户在数据Curry处理JSON格式数据。

用户不须要更变现有的表结构,因为SQL Server使用NVALANDCHA奥迪Q伍数据类型来存款和储蓄JSON文件,并且跟现有技术并行相配,比如全文字笔迹检查实验索、列存款和储蓄索引、in-memory
OLTP,应用程序不供给做任何改动

 

不要求动用JSON.Net那类工具分析和拍卖JSON数据,利用SQL
Server内置函数就足以处理JSON数据,轻松将查询结构输出为JSON格式,大概搜索JSON文件内容。

 


使用 JSON AUTO 输出JSON 格式

要将select语句的结果以JSON输出,最简便易行的艺术是在后面加上 FO奥迪Q7 JSON AUTO

测试版本

Microsoft SQL Server 2016 (CTP2.2) - 13.0.407.1 (X64)   Jul 22 2015 21:19:11   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

SELECT * FROM [dbo].[Client] 
GO

SELECT * FROM [dbo].[Client] FOR JSON AUTO
GO

 

图片 8

 

小编们能够把每列中展现的最大字符数 设置为81玖2

图片 9

图片 10

 


加上Root Key

借使想为FOXC90 JSON 加上Root Key,能够采取ROOT选项来钦定 Root Key 名称

SELECT * FROM [dbo].[Client] FOR JSON AUTO,ROOT('SUSU')
GO

图片 11

 


使用JSON PATH 输出JSON格式

当想要自定义输出JSON格式结构的时候,必须用JSON PATH描述,若SELECT
的字段名称一致,必须用外号格局来重新命名字段名那样才足以接二连三查询

除此以外,假诺字段的暗许值为NULL,那么输出JSON时,JSON会忽略null的只。要是要显得null值,可以加上INCLUDE_NULL_VALUES
选项(同样适用于JSON AUTO字句)

select * from  [dbo].[Client]

--FOR JSON PATH
SELECT * FROM [dbo].[Client] WHERE ClientID =2
FOR JSON PATH

[{"ClientID":2,"Firstname":"Peter","Lastname":"Nielsen","Birthdate":"1998-05-19T00:00:00","Email":"Peter@126.com","PhoneNumber":"+86-16326269674","Birthplace":"Stockholm","SocialSecurityNumber":"1901531234"}]

--FOR JSON PATH
SELECT * FROM [dbo].[Client] WHERE ClientID =4
FOR JSON PATH,INCLUDE_NULL_VALUES

[{"ClientID":4,"Firstname":"kade","Lastname":null,"Birthdate":"1980-01-06T00:00:00","Email":"Lotte@SOHU.com","PhoneNumber":"+86-16326269674","Birthplace":"Aalborg","SocialSecurityNumber":"1061234"}]

图片 12

 

 

越多关于JSON的效益

现阶段SQL Server 201陆 CTP2对此JSON的机能支撑依然相比较有限,例如内置处理JSON格式化的函数,

ISJSON(判断是不是是JSON格式)、JSON_VALUE(分析JSON文件并领取出值)
、OPENJSON(将JSON文件转换为常见数据表)

那一个功用要等到CTP3才能六续推出

 

更加多SQL Server201陆好用的效益,敬请期待o(∩_∩)o