使用TSQL查询和更新 JSON 数据

SQL Server 中 JSON_MODIFY 的使用

JSON是三个可怜流行的,用于数据调换的文本数据(textual
data)格式,主要用于Web和平运动动应用程序中。JSON 使用“键/值对”(Key:Value
pair)存款和储蓄数据,能够代表嵌套键值对和数组三种复杂数据类型,JSON仅仅使用逗号(引用Key)和中括号(引用数组成分),就能够路由到钦命的性质或成员,使用简便,功用强盛。在SQL
Server
二零一五本子中扶助JSON格式,使用Unicode字符类型表示JSON数据,并能对JSON数据进行求证,查询和退换。推荐一款JSON验证和格式化的在线工具:json
formatter。

Intro

SQL Server 提供了安置函数,用于查询和立异JSON数据,剖析JSON文本,如图:

SQL Server 从 二〇一五 领头援救了有些JSON操作,近日的类型里也是广大地方字段直接存成了
JSON,要求掌握一下怎么在SQL Server 中操作 JSON.

澳门贵宾会注册送豪礼 1

JSON扶助适用于 SQL Server 2014 及以上版本 和 Azure SQL Database。

一,定义和验证JSON数据

近年来在档案的次序中众多地点字段都以存三个 JSON
字符串,特别是本着部分有成都百货上千不分明的本性的数量以来,那样的新闻大家平时都位居一个
Extra 之类的字段保存八个 JSON
字符串,由于不明了非常大,很有一点都不小可能率会蒙受要改革这些 JSON
字符串布局的主题材料,在改进 JSON 对象的某一个值和改善 JSON
的布局时都会用到 JSON_MODIFY,即日就来介绍一下 JSON_MODIFY澳门贵宾会注册送豪礼, 的使用

应用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 State of Qatar,重返1,表示字符串是JSON数据;再次回到0,表示字符串不是JSON数据;重临NULL,表示
expression是NULL;

-- 适用 append 在数组中追加元素PRINT JSON_MODIFY(@testJsonValue, 'append $.skills', 'Azure');-- 无效用法,这样会把属性更新为字符串PRINT JSON_MODIFY(@testJsonValue, '$.skills', '["C#","T-SQL","Azure"]');-- 修改数组PRINT JSON_MODIFY(@testJsonValue, '$.skills', JSON_QUERY('["C#","T-SQL","Azure"]'));-- 修改数组某个元素PRINT JSON_MODIFY(@testJsonValue, '$.skills[0]', 'CSharp');-- 增加属嵌套PRINT JSON_MODIFY(@testJsonValue, '$.Likes', JSON_QUERY('{"Girl": "Alice"}'));PRINT JSON_MODIFY(@testJsonValue, '$.Likes', JSON_QUERY('{"Animals": ["Dog","Cat"]}'));-- 增加 bool 属性PRINT JSON_MODIFY(@testJsonValue, '$.ShowAwardsAfterQuizEnds', CONVERT(BIT, 'false'));PRINT JSON_MODIFY(@testJsonValue, '$.ShowAwardsAfterQuizEnds', CONVERT(BIT, 1));-- 增加 int 属性PRINT JSON_MODIFY(@testJsonValue, '$.TotalWinnersCount', CONVERT(INT, '20'));PRINT JSON_MODIFY(@testJsonValue, '$.TotalWinnersCount', 20);-- 删除属性PRINT JSON_MODIFY(@testJsonValue, '$.name', NULL);

二,JSON 数据的PATH 表达式

出口结果如下图:

Path 表明式分为两片段:Path Mode和Path,Path
Mode是可选的(optional),有二种格局:lax和strict。

More

1,Path Mode

注意:

在Path 表明式的起始,能够由此lax 或 strict 关键字显式评释Path
Mode,假如不注脚,私下认可的帕特h Mode是lax。在lax
情势下,假设path表明式出错,那么JSON函数重返NULL。在strict方式下,假诺Path表明式出错,那么JSON函数抛出错误;

即使要翻新的字段未有是四个 JSON 对象或者一个数组,那么需求选择
JSON_QUERY ,不然更新的呼应的属性就成了字符串了。
假若是要刨除某叁特天性,把某三个属性更新为 NULL 就能够 纵然要加进三个bool 类型的属性,须求把相应的值转变为 BIT 类型

2,Path 表达式

Reference

Path是探望JSON数码的路径,有四种运算符:

总结

  • $:代表全部JSON 数据的内容;
  • 逗号 . :表示JSON对象的成员,也称之为,字段(Field),或Key;
  • 中括号 [] :表示数组中的成分,成分的苗头地方是0;
  • Key Name:键的名字,通过Key Name引用对应的Value;假如Key
    Name中蕴藏空格,$,逗号,中括号,使用双引号;

以上所述是我给大家介绍的SQL Server 中 JSON_MODIFY
的施用,希望对我们持有助于,假诺我们有此外疑问请给自个儿留言,小编会及时回复我们的。在这里也特别感激大家对台本之家网址的扶持!假使你以为本文对您有扶植,款待转发,烦请表明出处,多谢!

举例说,犹如下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数组的第1个成分中,查询Key是Name的Item对应的数额,本例是约翰;
  • $.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

澳门贵宾会注册送豪礼 2

2,返回JSON数据(JSON_QUERY)

使用 JSON_QUERY ( expression [ , path ] 卡塔尔(قطر‎ 函数,依照帕特h
参数,再次来到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

澳门贵宾会注册送豪礼 3

四,通过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')  

澳门贵宾会注册送豪礼 4

五,将JSON数据调换为关联表

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

澳门贵宾会注册送豪礼 5澳门贵宾会注册送豪礼 6

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.

澳门贵宾会注册送豪礼 7澳门贵宾会注册送豪礼 8

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数据调换来关系表情势

澳门贵宾会注册送豪礼 9澳门贵宾会注册送豪礼 10

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,存款和储蓄以下数据:

澳门贵宾会注册送豪礼 11

1,以Auto 形式生成JSON格式

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

回来的数据格式是

澳门贵宾会注册送豪礼 12澳门贵宾会注册送豪礼 13

{  
   "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,以帕特h形式生成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')

回到的多寡格式是:

澳门贵宾会注册送豪礼 14澳门贵宾会注册送豪礼 15

{
"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数据:

澳门贵宾会注册送豪礼 16

遵照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
二〇一五中,暗中认可会将字段分组,

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_QUEEscortY(expression,pathState of Qatar要求expression必需是实用的,为制止JSON_QUEEscortY试行停业,对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

发表评论

电子邮件地址不会被公开。 必填项已用*标注