详解JSON壹:使用TSQL查询数据和翻新JSON数据

用于查询和更新JSON数据,使用Unicode字符类型表示JSON数据,使用nvarchar表示JSON数据,表示字符串不是JSON数据,XML数据最简单的格式是,最简单的XML格式

金沙国际唯一官网网址 5
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)
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)

在传递数据时,XML和JSON是最常用的数据格式,SQL
Server从很早的本子就从头协理XML格式,而对于JSON格式,SQL
Server从2016版本早先辅助。大超越4/8据库系统并不曾晋级到SQL Server
201陆版本,因而在传递格式化的多少时,平常依旧采纳XML格式。对自己来讲,查询和平消除析XML格式的多寡须要调整的知识点较多,MSDN上有关XML的文书档案,又意欲把XML的种种方面都教师地清晰,以至于内容繁杂,使学习进程变得紧Baba。小编可怜不喜欢读书那个不常用的数据结构,再说,在平时的数据库开采中,用到XML的地点也很少,可是,①旦在应用程序中用到XML,就只有头疼的份了,既然避不开XML,那就用最简易的艺术学习它,掌握它,使用它,以备不时之需。写那篇小说,正是以最简便易行的方式,分享XML最常用的行使方法。

OPENJSON函数是3个行集函数(RowSet),能够将JSON数据转变为涉及表,

示范,对JSON数据进行update,insert,delete和充实数据元素

通过cross apply
连接操作,把SubExpression节点下的享有数据都转移为关系型数据,并把该多少存款和储蓄到目前数据表#Expressions中:

譬如,有如下事关表,字段category包涵JSON数据:

  • Auto 格局:依照select语句中column的逐壹,自动生成JSON数据的格式;
  • Path 模式:使用column name的格式来生成JSON数据的格式,column
    name使用逗号分隔(dot-separated)表示组-成员涉及;
  • .   :选择当前节点;
  • ..  :选择当前节点的父节点;
  • 金沙国际唯一官网网址,/   :从根节点发轫;
  • //  :从相称选用的节点发轫选用,而不思索其职分;
  • *  :通配符,相配放肆字符,或专擅节点;
  • node()  :相称大肆节点,跟通配符 * 功效相似; 
  • @PropertyName :选择属性;
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

在Path 表达式的起先,能够由此lax 或 strict 关键字显式注脚Path
Mode,如果不注脚,私下认可的Path Mode是lax。在lax
形式下,若是path表明式出错,那么JSON函数重返NULL。在strict情势下,假设Path表明式出错,那么JSON函数抛出荒唐;

通过nodes()函数,再次来到SubExpression节点及其性情,由于单个节点中,属性名不容许重新,因而,在nodes()函数再次回到的单个节点中,无需经过xpath路由,直接获得当前节点的属性值,那样,能够在xpath表达式中一向钦点属性,不需求显式以“[1]”结尾。

View Code

1,定义和验证JSON数据

@xml.value(‘xpath’,’sql_data_type’),返回XML数据中单个本性的标量值,在运用value()函数时,xpath
参数必须内定重回的是单个值,而value()函数不会去check重回值的数额。

归来的数据如下,JsonData是3个字符串,SQL Server自动对其开始展览字符转码:

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')

①,XML数据格式的简便介绍

 

一,查询标量值(JSON_VALUE)

3,XML数据的查询(query()函数

View Code

金沙国际唯一官网网址 1

@xml.query(’xpath‘)函数,参数是路线表达式,重临XML数据类型的结果,该XML是非类型化(untyped)的。

金沙国际唯一官网网址 2

Path表达式查询的数据是:

一般情状下,固然xml数据唯有3个属性值,静态类型化(Static
typing)供给,xpath表明式也必须显式钦赐再次回到单个标量值,因而,必须钦命在xpath函数的最后增加”[1]“,平日的xpath表明式是”(xpath)[1]“。

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
)

壹,以Auto 方式生成JSON格式

select @xml.query('/Expression/SubExpression')

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

OPENJSON函数是3个行集函数(RowSet),能够将JSON数据转变为涉及表,

二,使用字符串对XML数据赋值

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
{ 
 "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"
  }
 ]
}
<Oprand ID="268819" Name="abuse" />
<Oprand ID="277029" Name="mongohq" />
<Oprand ID="516813" Name="access" />
<Oprand ID="283839" Name="reviews" />
<Oprand ID="697348" Name="retention" />
<Oprand ID="281556" Name="richfaces" />
<Oprand ID="2993766" Name="rgp" />
  • 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和type3列;

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

select @xml.query('/Expression/SubExpression/node()')
select @xml.query('/Expression/SubExpression/*')

Format Nested JSON Output with PATH Mode
(SQL
Server)

回去的多寡格式是

<SubExpression ID="1" OperandType="Tag" Operator="and">
  <Oprand ID="268819" Name="abuse" />
  <Oprand ID="277029" Name="mongohq" />
  <Oprand ID="516813" Name="access" />
</SubExpression>
<SubExpression ID="2" OperandType="Tag" Operator="and">
  <Oprand ID="283839" Name="reviews" />
  <Oprand ID="697348" Name="retention" />
</SubExpression>
<SubExpression ID="3" OperandType="Tag" Operator="not">
  <Oprand ID="281556" Name="richfaces" />
  <Oprand ID="2993766" Name="rgp" />
</SubExpression>

二,JSON中的数组

上述就是本文的全部内容,希望对我们的就学抱有帮忙,也盼望我们多多辅助脚本之家。

query()函数重临的结果如下,该查询结果是非类型化的XML数据。

1,空JSON

您只怕感兴趣的稿子:

  • jsonp跨域请求数据完毕手提式有线电话机号码查询实例分析
  • SQL
    Server中校查询结果转变为Json格式脚本分享
  • 询问json的数据结构的八种方法简单介绍
  • java使用天猫商城API读写json落成手提式有线电话机归属地查询成效代码
  • jQuery+JSON+jPlayer落成QQ空间音乐查询成效示例
  • 用函数模板,写八个简短快速的 JSON
    查询器的法子介绍
  • JS 实现Json查询的点子实例

金沙国际唯一官网网址 3

JSON in SQL Server 2016: Part 1 of
4

二,以Path情势生成JSON格式

  • NodeName:接纳钦点节点名及其全体子节点;
  • NodeName[N]:采用钦点节点集结的第N个节点;
  • NodeName[@Name]:采用当前节点中包括钦定属性的节点;

6,将关系表数据以JSON格式存款和储蓄

三,通过Path查询JSON数据

<SubExpression ID="1" OperandType="Tag" Operator="not">
  <Oprand ID="268819" Name="abuse" />
  <Oprand ID="277029" Name="mongohq" />
  <Oprand ID="516813" Name="access" />
</SubExpression>
<SubExpression ID="2" OperandType="Tag" Operator="not">
  <Oprand ID="283839" Name="reviews" />
  <Oprand ID="697348" Name="retention" />
</SubExpression>
<SubExpression ID="3" OperandType="Tag" Operator="not">
  <Oprand ID="281556" Name="richfaces" />
  <Oprand ID="2993766" Name="rgp" />
</SubExpression>
[
  {
    "Id": 12,
    "JsonData": "[{\"UnitPrice\":12, \"OrderQty\":1}]"
  }
]

帕特h是造访JSON数码的路径,有八种运算符:

在select
子句中,要是不在小名中把字段内定为属性,那么该字段的值作为节点值,节点值分为标量类型和XML类型;

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

二,JSON 数据的PATH 表达式

行使类型转变的目标,是为着把for xml
path再次回到的字符串转变来XML数据类型,那样,就可以以XML格式嵌入到上次的for
xml path的结构中,作为子节点:

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

伍,将JSON数据转变为关联表

select @xml.value('(/Expression/SubExpression[1]/@ID)[1]','int')
select @xml.value('(/Expression/SubExpression/@ID)[1]','int')

金沙国际唯一官网网址 4

2,Path 表达式

在门路表明式中,跟节点的挑选有关的表明式是:

使用 JSON_VALUE(expression , path ) 函数,从JSON数据,依据Path
参数再次回到标量值,重临的数目是宽字符类型,最大值Nvarchar(四千);要是非得重临大于nvarchar(五千)的数额,使用OpenJson行集函数。

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

到此,作品也该最终了,XML的极简用法已经总括了累累,在随后工作中国,假如用到XML时,翻开那篇小说,能够十分的快消除XML常见的多少查询和剖析难题,那样就够用了。

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.

 2,返回JSON数据(JSON_QUERY)

2,XPath路线表达式

二,以Path格局生成JSON格式,推荐使用path格局,尤其是在字段来源于多个表的气象下,控制JSON的格式**

  • $:表示JSON的剧情,是最外层大括号中的全体Item,本例是五个people数组,数组的下标是从0发轫的;
  • $.people[0]:表示people数组的首先要素:{ “name”: “Jane”,
    “surname”: null, “active”: true }
  • $.people[0].name
    :从people数组的第3个成分中,查询Key是Name的Item对应的多少,本例是John;
  • $.people[1].surname:people数组中部存在surname 字段,由于该Path
    表达式未有注解帕特h
    Mode,暗中认可值是lax,当Path表明式出现错误时,重返NULL;

演示代码如下,在value()函数中,直接钦赐属性值,表示收获当前节点的属性值:

Format JSON Output Automatically with
AUTO Mode (SQL
Server)

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

金沙国际唯一官网网址 5

回到的数码格式是

  • $:代表全体JSON 数据的始末;
  • 逗号 . :表示JSON对象的积极分子,也叫做,字段(菲尔德),或Key;
  • 中括号 [] :表示数组中的成分,成分的初叶地方是0;
  • Key Name:键的名字,通过Key Name来引用对应的Value;借使Key
    Name中包罗空格,$,逗号,中括号,使用双引号;
;with cte_Expressions as 
(
    select e.v.query('.') as Expression
        ,e.v.value('@ID','int') as ExpressionID
        ,e.v.value('@TaxonomyID','int') as TaxonomyID
    from @xml.nodes('/Expression') as e(v)
)
,cte_SubExpression as 
(
    select e.ExpressionID
        ,e.TaxonomyID
        ,se.SubExpression
        ,se.SubExpressionID
        ,se.OperandType
        ,se.Operator
    from cte_Expressions e
    cross apply
    (
        select t.v.query('.') as SubExpression
            ,t.v.value('@ID','int') as SubExpressionID
            ,t.v.value('@OperandType','varchar(16)') as OperandType
            ,t.v.value('@Operator','varchar(16)') as Operator
        from e.Expression.nodes('/Expression/SubExpression') as t(v)
    ) as se
)
select p.TaxonomyID
    ,p.ExpressionID
    ,p.SubExpressionID
    ,p.OperandType
    ,p.Operator
    ,d.OperandID
    ,d.OperandName
from cte_SubExpression p
cross apply
(
    select t.v.value('@ID','int') as OperandID
        ,t.v.value('@Name','varchar(32)') as OperandName
    from p.SubExpression.nodes('/SubExpression/Oprand') as t(v)
) as d

七,索引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_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

一,选用节点及其子节点

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
select id,
 name,
 category
from dbo.dt_json
for json auto,root('json')

View Code

[
  {
    "Id": 12,
    "JsonData": [{"UnitPrice":12, "OrderQty":1}]
  }
]
select id as 'book.id',
 name as 'book.name',
 category as 'product.category'
from dbo.dt_json
for json path,root('json')

XML Data (SQL
Server)

经过For JSON  Auto/Path,将关乎表数据存款和储蓄为JSON格式,

例如,有如下JSON 数据,通过帕特h表明式,能够路由到JSON的次第属性:

;with cte_Expressions as 
(
    select distinct ExpressionID
        ,TaxonomyID
    from #Expressions with(nolock)
) 
,cte_SubExpressions as
(
    select o.ExpressionID
        ,o.SubExpressionID
        ,o.OperandType
        ,o.Operator
    from #Expressions o with(nolock)
    group by o.ExpressionID
        ,o.SubExpressionID
        ,operandType
        ,o.Operator
)
select e.TaxonomyID as TaxonomyID
    ,e.ExpressionID as ExpressionID
    ,cast(
    (
        select o.SubExpressionID as '@ID'
            ,o.OperandType as '@OperandType'
            ,case o.Operator when '&' then 'and' else 'not' end as '@Operator'
            ,cast((
                select op.OperandID as '@ID'
                    ,op.OperandName as '@Name'
                from #Expressions op with(nolock)
                where o.ExpressionID=op.ExpressionID
                    and o.SubExpressionID=op.SubExpressionID
                for xml path('Oprand')            
            )as xml)
        from cte_SubExpressions o
        where o.ExpressionID=e.ExpressionID
        for xml path('SubExpression')
        ) as xml)
        as SubExpressions
from cte_Expressions e

View Code

{
"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"
}
}
]
}
nodes ('xpath') as table(column)  

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

金沙国际唯一官网网址 6

金沙国际唯一官网网址 7金沙国际唯一官网网址 8

动用for json再次来到JSON时,能够去掉外层的数组包装器 [],例如

四,通过Path修改JSON数据

2,接纳钦赐节点的全体子节点集合

归来的JSON结构如下,满足:

使用 JSON_QUERY ( expression [ , path ] ) 函数,依照Path
参数,再次回到JSON 数据(JSON
fragment);参数path是可选的(optional),假使不点名option参数,那么私下认可的path是$,即,重返整个JSON数据。

declare @xml xml
set @xml='
<Expression ID="1" TaxonomyID="1">
  <SubExpression ID="1" OperandType="Tag" Operator="and">
    <Oprand ID="268819" Name="abuse" />
    <Oprand ID="277029" Name="mongohq" />
    <Oprand ID="516813" Name="access" />
  </SubExpression>
  <SubExpression ID="2" OperandType="Tag" Operator="and">
    <Oprand ID="283839" Name="reviews" />
    <Oprand ID="697348" Name="retention" />
  </SubExpression>
  <SubExpression ID="3" OperandType="Tag" Operator="not">
    <Oprand ID="281556" Name="richfaces" />
    <Oprand ID="2993766" Name="rgp" />
  </SubExpression>
</Expression>'

JSON_QUE汉兰达Y(expression,path)
须求expression必须是可行的,为幸免JSON_QUE中华VY实行倒闭,对NULL值,要么保持NULL值,要么设置空JSON,而空JSONO是
[] 或  {},而不是空的字符。

ISJSON 函数的格式是: ISJSON ( expression )
,再次来到一,表示字符串是JSON数据;再次来到0,表示字符串不是JSON数据;再次来到NULL,表示
expression是NULL;

陆,把行集数据转载为XML数据(for xml path)

  • 修改现有的品质:依据参数path从JSON数据中找到钦定的性质,将该属性的Value修改为参数newValue,重返值是修改之后的JSON数据;
  • 新增加新的键值对(Key:Value
    pair):借使JSON数据中不存在钦赐的习性,那么依据参数Path,在钦命的不二等秘书籍上新添键值对;
  • 删除键值对(Key:Value
    pair):假若参数newValue的值是NULL,那么表示从JSON数据中去除钦定的性情;
  • append
    关键字:用于从JSON数组中,追加三个要素;

Path 表明式分为两局地:帕特h Mode和帕特h。Path
Mode是可选的(optional),有三种情势:lax和strict。

path(‘root’) 子句用于钦赐根节点;

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

金沙国际唯一官网网址 9

select t.v.query('.') as SubExpression
    ,t.v.value('@ID','int') as SubExpressionID
    ,t.v.value('@OperandType','varchar(16)') as OperandType
    ,t.v.value('@Operator','varchar(16)') as Operator
from @xml.nodes('/Expression/SubExpression') as t(v)

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

使用 JSON_VALUE(expression , path ) 函数,从JSON数据,遵照Path
参数再次来到标量值,再次回到的多寡是字符类型。

4,XML数据的查询(value()函数)