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

背景

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

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

SQL Server2016 原生支持JSON

 

SQL Server 2005 开始支持 XML 数据类型,提供原生的 XML数据类型、XML
索引以及各种管理 XML 或输出 XML 格式的函数。

在 SQL Server 时隔 4
个主要版本
之后,终于在 Microsoft Ignite 2015
大会上正式宣布,新一代的 SQL Server 2016
正式支持现在最流行的数据交换格式— JSON(JavaScript Object Notation)。

 

SQL Server 2016
对JSON的支持并不是增加一个JSON数据类型,而是提供一个更轻便的框架,帮助用户在数据库里处理JSON格式数据。

用户不需要更变现有的表结构,因为SQL Server使用NVARCHAR
数据类型来存储JSON文件,并且跟现有技术相互兼容,比如全文搜索、列存储索引、in-memory
OLTP,应用程序不需要做任何修改

 

不需要使用JSON.Net这类工具分析和处理JSON数据,利用SQL
Server内置函数就可以处理JSON数据,轻松将查询结构输出为JSON格式,或者搜索JSON文件内容。

 


使用 JSON AUTO 输出JSON 格式

要将select语句的结果以JSON输出,最简单的方法是在后面加上 FOR 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

 

图片 1

 

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

图片 2

图片 3

 


加上Root Key

如果想为FOR JSON 加上Root Key,可以使用ROOT选项来指定 Root Key 名称

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

图片 4

 


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

图片 5

 

 

更多关于JSON的功能

目前SQL Server 2016 CTP2
对于JSON的功能支持还是比较有限,例如内置处理JSON格式化的函数,

ISJSON(判断是否是JSON格式)、JSON_VALUE(分析JSON文件并提取出值)
、OPENJSON(将JSON文件转换为普通数据表)

这些功能要等到CTP3才能陆续推出

 

更多SQL Server2016好用的功能,敬请期待o(∩_∩)o 

JSON是一个非常流行的,用于数据交换的文本数据(textual
data)格式,主要用于Web和移动应用程序中。JSON 使用“键/值对”(Key:Value
pair)存储数据,能够表示嵌套键值对和数组两种复杂数据类型,JSON仅仅使用逗号(引用Key)和中括号(引用数组元素),就能路由到指定的属性或成员,使用简单,功能强大。在SQL
Server
2016版本中支持JSON格式,使用Unicode字符类型表示JSON数据,并能对JSON数据进行验证,查询和修改。推荐一款JSON验证和格式化的在线工具:json
formatter。

实例

    当使用查询这些已经有固定架构的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)

  

 

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

图片 6

 

用另一种方式来查询这条记录,前提是需要知道在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

  

 

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

图片 7

 

这个地方最关心就是查询条件和添加索引。设想一下我们打算去查询所有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))。需要转换返回值到一个时间字段中,然后分离年来筛选查询条件。实际执行计划如下:

图片 8

 

为了验证如何对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”:

图片 9

 

然后获取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'

  

实际执行计划如下:

图片 10

 

    加入一个计算列叫做“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建议的缺失索引:

图片 11

 

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

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

  

 

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

图片 12

 

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

SQL Server 提供了内置函数,用于查询和更新JSON数据,分析JSON文本,如图:

总结:

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

 

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

图片 13

一,定义和验证JSON数据

使用nvarchar存储JSON文本数据,通过函数 ISJSON(expression)
验证JSON数据是否有效。

declare @json nvarchar(max)
set @json = 
N'{
    "info":{  
      "type":1,
      "address":{  
        "town":"bristol",
        "county":"avon",
        "country":"england"
      },
      "tags":["sport", "water polo"]
   },
   "type":"basic"
}'

select isjson(@json)

ISJSON 函数的格式是: ISJSON ( expression )
,返回1,表示字符串是JSON数据;返回0,表示字符串不是JSON数据;返回NULL,表示
expression是NULL;

二,JSON 数据的PATH 表达式

Path 表达式分为两部分:Path Mode和Path,Path
Mode是可选的(optional),有两种模式:lax和strict。

1,Path Mode

在Path 表达式的开始,可以通过lax 或 strict 关键字显式声明Path
Mode,如果不声明,默认的Path Mode是lax。在lax
模式下,如果path表达式出错,那么JSON函数返回NULL。在strict模式下,如果Path表达式出错,那么JSON函数抛出错误;

2,Path 表达式

Path是访问JSON数据的途径,有四种运算符:

  • $:代表整个JSON 数据的内容;
  • 逗号 . :表示JSON对象的成员,也叫做,字段(Field),或Key;
  • 中括号 [] :表示数组中的元素,元素的起始位置是0;
  • Key Name:键的名字,通过Key Name引用对应的Value;如果Key
    Name中包含空格,$,逗号,中括号,使用双引号;

例如,有如下JSON 数据,通过Path表达式,能够路由到JSON的各个属性:

{ "people":  
  [  
    { "name": "John", "surname": "Doe" },  
    { "name": "Jane", "surname": null, "active": true }  
  ]  
} 

Path表达式查询的数据是:

  • $:表示JSON的内容,是最外层大括号中的所有Item,本例是一个people数组,数组的下标是从0开始的;
  • $.people[0]:表示people数组的第一元素:{
    “name”: “Jane”, “surname”: null, “active”: true }
  • $.people[0].name
    :从people数组的第一个元素中,查询Key是Name的Item对应的数据,本例是John;
  • $.people[1].surname:people数组中部存在surname
    字段,由于该Path 表达式没有声明Path
    Mode,默认值是lax,当Path表达式出现错误时,返回NULL;

三,通过Path查询JSON数据

1,查询标量值(JSON_VALUE)

使用 JSON_VALUE(expression , path ) 函数,从JSON数据,根据Path
参数返回标量值,返回的数据是宽字符类型,最大值Nvarchar(4000);如果必须返回大于nvarchar(4000)的数据,使用OpenJson行集函数。

declare @json nvarchar(max)
set @json = 
N'{
    "info":{  
      "type":1,
      "address":{  
        "town":"bristol",
        "county":"avon",
        "country":"england"
      },
      "tags":["sport", "water polo"]
   },
   "type":"basic"
}'

select
  json_value(@json, '$.type') as type,
  json_value(@json, '$.info.type') as info_type,
  json_value(@json, '$.info.address.town') as town,
  json_value(@json, '$.info.tags[0]') as tag

图片 14

2,返回JSON数据(JSON_QUERY)

使用 JSON_QUERY ( expression [ , path ] ) 函数,根据Path
参数,返回JSON 数据(JSON
fragment);参数path是可选的(optional),如果不指定option参数,那么默认的path是$,即,返回整个JSON数据。

declare @json nvarchar(max)
set @json = 
N'{
    "info":{  
      "type":1,
      "address":{  
        "town":"bristol",
        "county":"avon",
        "country":"england"
      },
      "tags":["sport", "water polo"]
   },
   "type":"basic"
}'

select
    json_query(@json, '$') as json_context,
    json_query(@json, '$.info') as info,
    json_query(@json, '$.info.address') as info_address,
    json_query(@json, '$.info.tags') as info_tags

图片 15

四,通过Path修改JSON数据

使用 JSON_MODIFY ( expression , path , newValue )
修改JSON数据中的属性值,并返回修改之后的JSON数据,该函数修改JSON数据的流程是:

  • 修改现有的属性:按照参数path从JSON数据中找到指定的属性,将该属性的Value修改为参数newValue,返回值是修改之后的JSON数据;
  • 新增新的键值对(Key:Value
    pair):如果JSON数据中不存在指定的属性,那么按照参数Path,在指定的路径上新增键值对;
  • 删除键值对(Key:Value
    pair):如果参数newValue的值是NULL,那么表示从JSON数据中删除指定的属性;
  • append
    关键字:用于从JSON数组中,追加一个元素;

示例,对JSON数据进行update,insert,delete和追加数据元素

declare @info nvarchar(100) = '{"name":"john","skills":["c#","sql"]}'  
-- update name  
set @info = json_modify(@info, '$.name', 'mike')  
-- insert surname  
set @info = json_modify(@info, '$.surname', 'smith')  
-- delete name  
set @info = json_modify(@info, '$.name', null)  
-- add skill  
set @info = json_modify(@info, 'append $.skills', 'azure')  

图片 16

五,将JSON数据转换为关系表

OPENJSON函数是一个行集函数(RowSet),能够将JSON数据转换为关系表,

图片 17图片 18

OPENJSON( jsonExpression [ , path ] )  
[  
   WITH (   
      colName type [ column_path ] [ AS JSON ]  
   [ , colName type [ column_path ] [ AS JSON ] ]  
   [ , . . . n ]   
      )  
] 

View Code

  • path 参数:也叫table
    path,指定关系表在JSON数据中的路径;
  • column_path
    参数:基于path参数,指定每个column在关系表JSON中的路径,应总是显式指定column
    path;
  • AS JSON 属性:如果指定AS
    JSON属性,那么
    column的数据类型必须定义为nvarchar(max),表示该column的值是JSON数据;如果不指定AS
    JSON属性,那么该Column的值是标量值;
  • with
    选项:指定关系表的Schema,应总是指定with选项;如果不指定with
    选项,那么函数返回key,value和type三列;

1,示例,从JSON数据中,以关系表方式呈现数据

declare @json nvarchar(max)
set @json = 
N'{
    "info":{  
      "type":1,
      "address":{  
        "town":"bristol",
        "county":"avon",
        "country":"england"
      },
      "tags":["sport", "water polo"]
   },
   "type":"basic"
}'

SELECT info_type,info_address,tags
FROM OPENJSON(@json, '$.info') 
with 
(
info_type tinyint 'lax $.type',
info_address nvarchar(max) 'lax $.address' as json,
tags nvarchar(max) 'lax $.tags' as json
)

2,OpenJSON
函数的另外一个功能是遍历数组,为数组中的每一个元素产生一个数据行

When you use OPENJSON with an
explicit schema, the function returns a table with the schema that you
defined in the WITH clause. In the WITH clause, you define columns,
their types, and the paths of the source properties for each
column.

  • For each element in the array in the
    input expression, OPENJSON generates a separate row in the
    output table.

  • For each property of the array
    elements specified by using
    the colName type column_path syntax, OPENJSON converts the
    value to the specified type and populates a cell in the output
    table.

图片 19图片 20

SET @json = N'{"Orders":   
  {"OrdersArray":  
    [  
      {  
        "Order": {  
          "Number":"SO43659",  
          "Date":"2011-05-31T00:00:00"  
        },  
        "AccountNumber":"AW29825",  
        "Item": {  
          "Price":2024.9940,  
          "Quantity":1  
        }  
      },  
      {  
        "Order": {  
          "Number":"SO43661",  
          "Date":"2011-06-01T00:00:00"  
        },  
        "AccountNumber":"AW73565",  
        "Item": {  
          "Price":2024.9940,  
          "Quantity":3  
        }  
      }  
    ]  
  }  
}'  

SELECT t.* 
FROM  
OPENJSON ( @json, '$.Orders.OrdersArray' )  
WITH (   
             Number   varchar(200)   '$.Order.Number',  
             Date     datetime       '$.Order.Date',  
             Customer varchar(200)   '$.AccountNumber',  
             Quantity int            '$.Item.Quantity',  
             [Order]  nvarchar(MAX)  AS JSON  
) as t

View Code

3,OpenJSON 函数搭配Apply使用,为表中的JSON数据转换成关系表形式

图片 21图片 22

select t.*,sl.result,sl.time
from [dbo].[WebPages] sl 
cross apply openjson(JSON_QUERY(Parameters,'$.CategoryList'))
with
(
    ID varchar(64) '$.ID',
    name varchar(64) '$.Name',
    Type varchar(64) '$.Type'
)
 as t
where sl.action='New Product' and t.Type in('Blogs','Forums')
order by sl.time desc

View Code

六,将关系表数据以JSON格式存储

通过For JSON  Auto/Path,将关系表数据存储为JSON格式,

  • Auto 模式:根据select语句中column的顺序,自动生成JSON数据的格式;
  • Path 模式:使用column name的格式来生成JSON数据的格式,column
    name使用逗号分隔(dot-separated)表示组-成员关系;

示例,有表:dt_json,存储以下数据:

图片 23

1,以Auto 模式生成JSON格式

select id,
    name,
    category
from dbo.dt_json
for json auto,root('json')

返回的数据格式是

图片 24图片 25

{  
   "json":[  
      {  
         "id":1,
         "name":"C#",
         "category":"Computer"
      },
      {  
         "id":2,
         "name":"English",
         "category":"Language"
      },
      {  
         "id":3,
         "name":"MSDN",
         "category":"Web"
      },
      {  
         "id":4,
         "name":"Blog",
         "category":"Forum"
      }
   ]
}

View Code

2,以Path模式生成JSON格式,推荐使用path模式,特别是在字段来源于多个表的情况下,控制JSON的格式**

select id as 'book.id',
    name as 'book.name',
    category as 'product.category'
from dbo.dt_json
for json path,root('json')

返回的数据格式是:

图片 26图片 27

{
"json":[
{
"book":{
"id":1,
"name":"C#"
},
"product":{
"category":"Computer"
}
},
{
"book":{
"id":2,
"name":"English"
},
"product":{
"category":"Language"
}
},
{
"book":{
"id":3,
"name":"MSDN"
},
"product":{
"category":"Web"
}
},
{
"book":{
"id":4,
"name":"Blog"
},
"product":{
"category":"Forum"
}
}
]
}

View Code

七,索引JSON数据

JSON文本不是内置的数据类型,没有专门的JSON索引,但是,可以通过创建计算列和标准B-Tree索引提高查询JSON数据的性能,避免全表扫描(Full
Table Scan),通过索引计算列,间接实现对JSON进行查找。

索引JSON数据的Workaround是:为查询条件(Filter)创建计算列,使用persisted属性持久存储;在计算列上创建索引,使用包含列(Include)包含特定的字段,以避免键值查找(Key
Lookup),提高索引查找的性能。

例如,有如下关系表,字段category包含JSON数据:

图片 28

按照type属性过滤,包含name字段,创建索引的示例是:

alter table dbo.dt_json
add category_type as (cast(json_value(category,'$.type') as int)) persisted;

create nonclustered index idx_dt_json_category_type
on dbo.dt_json
(
category_type
)
include(name);

八,JSON查询技巧

1,使用Path模式,控制JSON结构的Path(层次)

当字段来源于多个Table时,使用Auto模式,在SQL Server
2016中,默认会将字段分组,

select top 3 t.name
    ,o.object_id
    ,o.type
from sys.objects o 
inner join sys.tables t 
    on o.object_id=t.object_id
for json auto

返回的结果是,多了一个层次:

[{"name":"table_1","o":[{"object_id":27147142,"type":"U "}]},
{"name":"table_2","o":[{"object_id":87671360,"type":"U "}]},
{"name":"table_3","o":[{"object_id":91147370,"type":"U "}]}]

使用Path模式(for json path),path是根据列的别名来定义Json的层次

[{"name":"table_1","object_id":27147142,"type":"U "},
{"name":"table_2","object_id":87671360,"type":"U "},
{"name":"table_3","object_id":91147370,"type":"U "}]

2,嵌套JSON结构

在查询时,Table_2的JsonData字段是个Json数据,需要嵌套到另一个JSON中,例如:[{“UnitPrice”:12,
“OrderQty”:1}],如果在外层JSON结构中,嵌套一个内层的JSON结构:

select t1.ID
    ,t2.JsonData
from dbo.table_1 t1
inner join dbo.table_2 t2
    on ...
for json path

返回的数据如下,JsonData是一个字符串,SQL Server自动对其进行字符转码:

[
  {
    "Id": 12,
    "JsonData": "[{"UnitPrice":12, "OrderQty":1}]"
  }
]

在嵌套的JSON上,使用JSON_Query(expression,path),返回数据,然后再对其进行JSON
格式:

select t1.ID
    ,json_query(t2.JsonData) as JsonData
from dbo.table_1 t1
inner join dbo.table_2 t2
    on ...
for json path

返回的JSON结构如下,满足:

[
  {
    "Id": 12,
    "JsonData": [{"UnitPrice":12, "OrderQty":1}]
  }
]

 

九,编程注意事项

1,空JSON

JSON_QUERY(expression,path)
要求expression必须是有效的,为避免JSON_QUERY执行失败,对NULL值,要么保持NULL值,要么设置空JSON,而空JSONO是
[] 或  {},而不是空的字符。

2,JSON中的数组

在查询时,经常会返回JSON数组,使用[index]来遍历数组元素,数组下标从0开始,例如,以下JSON数组,及其查询示例:

[
{...}
]
--path expression
lax $[0]

使用for json返回JSON时,可以去掉外层的数组包装器 [],例如

for json path,without_array_wrapper

 

 

 

参考文档:

JSON Data (SQL
Server)

JSON Path Expressions (SQL
Server)

JSON Functions
(Transact-SQL)

OPENJSON
(Transact-SQL)

Index JSON
data

Format Query Results as JSON with FOR
JSON (SQL
Server)

Format Nested JSON Output with PATH Mode
(SQL
Server)

Format JSON Output Automatically with
AUTO Mode (SQL
Server)

JSON Support in SQL Server
2016

JSON in SQL Server 2016: Part 1 of
4

网站地图xml地图