JSON是一个非常流行的，用于数据交换的文本数据（textual data）格式，主要用于Web和移动应用程序中。JSON 使用“键/值对”（Key:Value pair）存储数据，能够表示嵌套键值对和数组两种复杂数据类型，JSON仅仅使用逗号（引用Key）和中括号（引用数组元素），就能路由到指定的属性或成员，使用简单，功能强大。在SQL Server 2016版本中支持JSON格式，使用Unicode字符类型表示JSON数据，并能对JSON数据进行验证，查询和修改。推荐一款JSON验证和格式化的在线工具：json formatter。
SQL Server 提供了内置函数，用于查询和更新JSON数据，分析JSON文本，如图：
一，定义和验证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 数据，通过Path表达式，能够路由到JSON的各个属性：
{ "people": [ { "name": "John", "surname": "Doe" }, { "name": "Jane", "surname": null, "active": true } ] } 复制代码
Path表达式查询的数据是：
三，通过Path查询JSON数据
1，查询标量值（JSON_VALUE）
使用 JSON_VALUE(expression , path ) 函数，从JSON数据，根据Path 参数返回标量值，返回的数据是字符类型。
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，返回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复制代码
四，通过Path修改JSON数据
使用 JSON_MODIFY ( expression , path , newValue ) 修改JSON数据中的属性值，并返回修改之后的JSON数据，该函数修改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') 复制代码
五，将JSON数据转换为关系表
OPENJSON函数是一个行集函数（RowSet），能够将JSON数据转换为关系表，
OPENJSON( jsonExpression [ , path ] ) [ WITH (
colName type [ column_path ] [ AS JSON ] [ , colName type [ column_path ] [ AS JSON ] ] [ , . . . n ]
) ] 复制代码
View Code
示例，从JSON数据中，以关系表方式呈现数据
相关文章
精彩导读
热门资讯
关注我们
