JSON是一個非常流行的，用于數(shù)據(jù)交換的數(shù)據(jù)格式，主要用于Web和移動應(yīng)用程序中。JSON 使用鍵/值對（Key:Value pair）存儲數(shù)據(jù)，并且表示嵌套鍵值對和數(shù)組兩種復(fù)雜數(shù)據(jù)類型，僅僅使用逗號（引用Key）和中括號（引用數(shù)組元素），就能路由到指定的屬性或成員，使用簡單，功能強(qiáng)大。在SQL Server 2016版本中支持JSON格式，使用Unicode字符類型表示JSON數(shù)據(jù)，并能對JSON數(shù)據(jù)進(jìn)行驗證，查詢和修改。推薦一款JSON驗證和格式化的工具：json formatter。

一，定義和驗證JSON數(shù)據(jù)

使用nvarchar表示JSON數(shù)據(jù)，通過函數(shù)ISJSON函數(shù)驗證JSON數(shù)據(jù)是否有效。

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 函數(shù)的格式是: ISJSON ( expression ) ，返回1，表示字符串是JSON數(shù)據(jù)；返回0，表示字符串不是JSON數(shù)據(jù)；返回NULL，表示 expression是NULL；

二，JSON 數(shù)據(jù)的PATH 表達(dá)式

Path 表達(dá)式分為兩部分：Path Mode和Path。Path Mode是可選的（optional），有兩種模式：lax和strict。

1，Path Mode

在Path 表達(dá)式的開始，可以通過lax 或 strict 關(guān)鍵字顯式聲明Path Mode，如果不聲明，默認(rèn)的Path Mode是lax。在lax 模式下，如果path表達(dá)式出錯，那么JSON函數(shù)返回NULL。在strict模式下，如果Path表達(dá)式出錯，那么JSON函數(shù)拋出錯誤；

2，Path 表達(dá)式

Path是訪問JSON數(shù)據(jù)的途徑，有四種運(yùn)算符：

• $：代表整個JSON 數(shù)據(jù)的內(nèi)容；
• 逗號 . ：表示JSON對象的成員，也叫做，字段（Field），或Key；
• 中括號 [] ：表示數(shù)組中的元素，元素的起始位置是0；
• Key Name：鍵的名字，通過Key Name來引用對應(yīng)的Value；如果Key Name中包含空格，$，逗號，中括號，使用雙引號；
  

例如，有如下JSON 數(shù)據(jù)，通過Path表達(dá)式，能夠路由到JSON的各個屬性：

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

Path表達(dá)式查詢的數(shù)據(jù)是：

• $：表示JSON的內(nèi)容，是最外層大括號中的所有Item，本例是一個people數(shù)組，數(shù)組的下標(biāo)是從0開始的；
• $.people[0]：表示people數(shù)組的第一元素：{ "name": "Jane", "surname": null, "active": true }
• $.people[0].name ：從people數(shù)組的第一個元素中，查詢Key是Name的Item對應(yīng)的數(shù)據(jù)，本例是John；
• $.people[1].surname：people數(shù)組中部存在surname 字段，由于該P(yáng)ath 表達(dá)式?jīng)]有聲明Path Mode，默認(rèn)值是lax，當(dāng)Path表達(dá)式出現(xiàn)錯誤時，返回NULL；

三，通過Path查詢JSON數(shù)據(jù)

1，查詢標(biāo)量值（JSON_VALUE）

使用 JSON_VALUE(expression , path ) 函數(shù)，從JSON數(shù)據(jù)，根據(jù)Path 參數(shù)返回標(biāo)量值，返回的數(shù)據(jù)是字符類型。

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數(shù)據(jù)（JSON_QUERY）

使用 JSON_QUERY ( expression [ , path ] ) 函數(shù)，根據(jù)Path 參數(shù)，返回JSON 數(shù)據(jù)（JSON fragment)；參數(shù)path是可選的（optional），如果不指定option參數(shù)，那么默認(rèn)的path是$，即，返回整個JSON數(shù)據(jù)。

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數(shù)據(jù)

使用 JSON_MODIFY ( expression , path , newValue ) 修改JSON數(shù)據(jù)中的屬性值，并返回修改之后的JSON數(shù)據(jù)，該函數(shù)修改JSON數(shù)據(jù)的流程是：

• 修改現(xiàn)有的屬性：按照參數(shù)path從JSON數(shù)據(jù)中找到指定的屬性，將該屬性的Value修改為參數(shù)newValue，返回值是修改之后的JSON數(shù)據(jù)；
• 新增新的鍵值對（Key：Value pair）：如果JSON數(shù)據(jù)中不存在指定的屬性，那么按照參數(shù)Path，在指定的路徑上新增鍵值對；
• 刪除鍵值對（Key：Value pair）：如果參數(shù)newValue的值是NULL，那么表示從JSON數(shù)據(jù)中刪除指定的屬性；
• append 關(guān)鍵字：用于從JSON數(shù)組中，追加一個元素；
  

示例，對JSON數(shù)據(jù)進(jìn)行update，insert，delete和追加數(shù)據(jù)元素

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數(shù)據(jù)轉(zhuǎn)換為關(guān)系表

OPENJSON函數(shù)是一個行集函數(shù)（RowSet），能夠?qū)SON數(shù)據(jù)轉(zhuǎn)換為關(guān)系表，

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

• path 參數(shù)：也叫table path，指定關(guān)系表在JSON數(shù)據(jù)中的路徑；
• column_path 參數(shù)：基于path參數(shù)，指定每個column在關(guān)系表JSON中的路徑，應(yīng)總是顯式指定column path；
• AS JSON 屬性：如果指定AS JSON屬性，那么 column的數(shù)據(jù)類型必須定義為nvarchar(max)，表示該column的值是JSON數(shù)據(jù)；如果不指定AS JSON屬性，那么該Column的值是標(biāo)量值；
• with 選項：指定關(guān)系表的Schema，應(yīng)總是指定with選項；如果不指定with 選項，那么函數(shù)返回key，value和type三列；
  

示例，從JSON數(shù)據(jù)中，以關(guān)系表方式呈現(xiàn)數(shù)據(jù)

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
)

六，將關(guān)系表數(shù)據(jù)以JSON格式存儲

通過For JSON  Auto/Path，將關(guān)系表數(shù)據(jù)存儲為JSON格式，

• Auto 模式：根據(jù)select語句中column的順序，自動生成JSON數(shù)據(jù)的格式；
• Path 模式：使用column name的格式來生成JSON數(shù)據(jù)的格式，column name使用逗號分隔（dot-separated）表示組-成員關(guān)系；

1，以Auto 模式生成JSON格式

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

返回的數(shù)據(jù)格式是

{ 
 "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"
  }
 ]
}

2，以Path模式生成JSON格式

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

返回的數(shù)據(jù)格式是：

{
"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"
}
}
]
}

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。