您的位置:68399皇家赌场 > 虚拟主机 > SQL Server二零一六 原生补助JSON

SQL Server二零一六 原生补助JSON

发布时间:2019-08-17 17:14编辑:虚拟主机浏览(86)

    实例

        当使用查询那个已经有固定架构的JSON的数额表时,使用“FOR JSON” 提示在你的T-SQL脚本后边,用这种方法以便于格式化输出。一下实例作者使用了SQLServer 二零一四 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凯雷德类型。Application.People 表中的CustomFields 列正是出一头地这种景况。能够用如下语句看一下报表格式这几个列的开始和结果:

    declare @json nvarchar(max)
    
    SELECT @json=[CustomFields]
    FROM [WideWorldImporters].[Application].[People]
    where PersonID=8
    
    select * from openjson(@json)
    

      

     

    结果集在表格结果中的呈现:

    澳门皇家赌场55533网址 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
    

      

     

    在报表结果聚焦体现表格格式的结果:

    澳门皇家赌场55533网址 2

     

    本条地点最关切正是查询条件和加多索引。虚构一下我们希图去查询全数2012年之后雇佣的人,你可以运营下边包车型大巴查询语句:

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

    澳门皇家赌场55533网址 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”:

    澳门皇家赌场55533网址 4

     

    然后拿走二零一四年12月的小票的数量额:

    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'
    

      

    实际实践布署如下:

    澳门皇家赌场55533网址 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提议的缺点和失误索引:

    澳门皇家赌场55533网址 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
    

      

     

    大家再一次实施查询证实推行安插:

    澳门皇家赌场55533网址 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 2015对JSON的支撑并非充实一个JSON数据类型,而是提供一个更轻易的框架,支持用户在数据Curry管理JSON格式数据。

    用户不需求更变现成的表结构,因为SQL Server使用NVARCHA奇骏数据类型来存款和储蓄JSON文件,何况跟现存工夫并行合营,举例全文字笔迹核实索、列存款和储蓄索引、in-memory OLTP,应用程序无需做任何改造

     

    无需运用JSON.Net那类工具剖判和拍卖JSON数据,利用SQL Server内置函数就足以管理JSON数据,轻巧将查询结构输出为JSON格式,或许寻找JSON文件内容。

     


    使用 JSON AUTO 输出JSON 格式

    要将select语句的结果以JSON输出,最简易的措施是在末端加上 FOPAJERO 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
    

     

    澳门皇家赌场55533网址 8

     

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

    澳门皇家赌场55533网址 9

    澳门皇家赌场55533网址 10

     


    加上Root Key

    假定想为FO陆风X8 JSON 加上Root Key,能够行使ROOT选项来内定 Root Key 名称

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

    澳门皇家赌场55533网址 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"}]
    

    澳门皇家赌场55533网址 12

     

     

    更加的多关于JSON的效率

    现阶段SQL Server 二零一四 CTP2 对此JSON的职能帮忙依旧相比有限,比方内置管理JSON格式化的函数,

    ISJSON(判别是或不是是JSON格式)、JSON_VALUE(剖判JSON文件并提抽取值) 、OPENJSON(将JSON文件调换为普通数据表)

    澳门皇家赌场55533网址,那个效应要等到CTP3技巧时有时无推出

     

    越来越多SQL Server2014好用的效用,敬请期待o(∩_∩)o 

    SQL Server2014 原生援救JSON

     

    SQL Server 2007 初始接济 XML 数据类型,提供原生的 XML数据类型、XML 索引以及各样管理 XML 或输出 XML 格式的函数。

    在 SQL Server 时隔 4 个基本点版本尔后,终于在 Microsoft Ignite 2014大会上专门的职业公布,新一代的 SQL Server 二零一六正式扶助未来最风靡的数据调换格式— JSON(JavaScript Object Notation)。

     

    SQL Server 二〇一五对JSON的支撑实际不是扩充一个JSON数据类型,而是提供三个更轻易的框架,帮忙用户在数据Curry管理JSON格式数据。

    用户不供给更变现成的表结构,因为SQL Server使用NVARCHALX570数据类型来存款和储蓄JSON文件,而且跟现存技术并行相称,比如全文字笔迹核查索、列存款和储蓄索引、in-memory OLTP,应用程序不必要做任何修改

     

    无需使用JSON.Net那类工具分析和管理JSON数据,利用SQL Server内置函数就足以管理JSON数据,轻巧将查询结构输出为JSON格式,可能搜索JSON文件内容。

     


    使用 JSON AUTO 输出JSON 格式

    要将select语句的结果以JSON输出,最轻易易行的议程是在前面加上 FO景逸SUV 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
    

     

    澳门皇家赌场55533网址 13

     

    咱俩能够把每列中展现的最大字符数 设置为8192

    澳门皇家赌场55533网址 14

    澳门皇家赌场55533网址 15

     


    加上Root Key

    固然想为FO奔驰M级 JSON 加上Root Key,能够运用ROOT选项来钦赐 Root Key 名称

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

    澳门皇家赌场55533网址 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"}]
    

    澳门皇家赌场55533网址 17

     

     

    更加的多关于JSON的成效

    当前SQL Server 2014 CTP2 对于JSON的坚守辅助依然相比较单薄,比如内置管理JSON格式化的函数,

    ISJSON(剖断是或不是是JSON格式)、JSON_VALUE(深入分析JSON文件并提抽出值) 、OPENJSON(将JSON文件调换为普通数据表)

    这几个效应要等到CTP3本领时断时续推出

     

    愈来愈多SQL Server2014好用的功效,敬请期待o(∩_∩)o 

    本文由68399皇家赌场发布于虚拟主机,转载请注明出处:SQL Server二零一六 原生补助JSON

    关键词: 68399皇家赌场 数据库技术 所有随笔 SQLServer201

上一篇:大数量查询优化

下一篇:没有了