SQL Server二零一四 原生支持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格式的字符类型,改列的品类能够使NVARCHA中华V类型。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

 

本条地点最关切就是查询条件和增加索引。设想一下我们筹划去询问全部二零一三年之后雇佣的人,你能够运营下边包车型大巴查询语句:

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(5000))。供给转移再次来到值到多个时刻字段中,然后分别年来筛选查询条件。实际奉行布置如下:

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

 

接下来拿走二〇一五年四月的发票数额:

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 Server二零一五 原生援救JSON

 

SQL Server 二零零七 开端帮助 XML 数据类型,提供原生的 XML数据类型、XML 索引以及各类管理 XML 或输出 XML 格式的函数。

在 SQL Server 时隔 4 个关键版本日后,终于在 Microsoft Ignite 二零一六大会上标准透露,新一代的 SQL Server 二零一六正式辅助今后最风靡的数据交流格式— JSON(JavaScript Object Notation)。

 

SQL Server 2014对JSON的支撑并非扩充贰个JSON数据类型,而是提供贰个更轻松的框架,援助用户在数据Curry管理JSON格式数据。

用户无需更变现存的表结构,因为SQL Server使用NVARCHA牧马人数据类型来存款和储蓄JSON文件,並且跟现成工夫并行协作,举例全文字笔迹核算索、列存款和储蓄索引、in-memory OLTP,应用程序无需做任何改造

 

无需运用JSON.Net那类工具解析和拍卖JSON数据,利用SQL Server内置函数就可以拍卖JSON数据,轻巧将查询结构输出为JSON格式,只怕寻觅JSON文件内容。

 


使用 JSON AUTO 输出JSON 格式

要将select语句的结果以JSON输出,最简易的措施是在后面加上 FO普拉多 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

 

大家可以把每列中显示的最大字符数 设置为8192

图片 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 2016 CTP2 对此JSON的效果支撑还是相比较单薄,举例内置管理JSON格式化的函数,

ISJSON(剖断是还是不是是JSON格式)、JSON_VALUE(剖判JSON文件并领抽出值) 、OPENJSON(将JSON文件转变为普通数据表)

这几个功用要等到CTP3技巧时有时无推出

 

更加的多SQL Server二〇一四好用的作用,敬请期待o(∩_∩)o 

SQL Server二〇一六 原生辅助JSON

 

SQL Server 二〇〇五 起头辅助 XML 数据类型,提供原生的 XML数据类型、XML 索引以及各类管理 XML 或输出 XML 格式的函数。

在 SQL Server 时隔 4 个相当重要版本事后,终于在 Microsoft Ignite 贰零壹伍大会上职业发表,新一代的 SQL Server 2014正式支持今后最流行的数据交流格式— JSON(JavaScript Object Notation)。

 

SQL Server 贰零壹陆对JSON的支撑实际不是充实一个JSON数据类型,而是提供一个更轻易的框架,帮衬用户在数据Curry管理JSON格式数据。

用户无需更变现成的表结构,因为SQL Server使用NVARCHALAND数据类型来储存JSON文件,何况跟现成本事并行同盟,比方全文字笔迹核查索、列存款和储蓄索引、in-memory OLTP,应用程序不须要做任何修改

 

没有须求使用JSON.Net那类工具深入分析和管理JSON数据,利用SQL Server内置函数就能够拍卖JSON数据,轻巧将查询结构输出为JSON格式,只怕搜索JSON文件内容。

 


使用 JSON AUTO 输出JSON 格式

要将select语句的结果以JSON输出,最简便的方法是在背后加上 FO中华V 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

 

图片 13

 

大家得以把每列中展现的最大字符数 设置为8192

图片 14

图片 15

 


加上Root Key

即使想为FO路虎极光 JSON 加上Root Key,能够行使ROOT选项来钦定 Root Key 名称

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

图片 16

 


使用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"}]

图片 17

 

 

更加多关于JSON的功力

当前SQL Server 二零一六 CTP2 对于JSON的功用支持如故相比单薄,举个例子内置管理JSON格式化的函数,

ISJSON(剖断是不是是JSON格式)、JSON_VALUE(剖判JSON文件并提抽出值) 、OPENJSON(将JSON文件转变为平时数据表)

那个效应要等到CTP3能力陆陆续续推出

 

愈来愈多SQL Server二零一五好用的功力,敬请期待o(∩_∩)o 

本文由ca88发布,转载请注明来源

关键词: ca88网址 数据库技术 所有随笔 SQLServer201 sqlserv