JSON是一個非常流行的，用於資料交換的資料格式，主要用於Web和行動應用程式。 JSON 使用鍵/值對（Key:Value pair）儲存數據，並且表示巢狀鍵值對和陣列兩種複雜資料類型，僅使用逗號（引用Key）和中括號（引用陣列元素），就能路由到指定的屬性或成員，使用簡單，功能強大。在SQL Server 2016版本中支援JSON格式，使用Unicode字元類型表示JSON數據，並能對JSON資料進行驗證，查詢與修改。推薦一個JSON驗證和格式化的工具：json formatter。
一，定義和驗證JSON數據
使用nvarchar表示JSON數據，透過函數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)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 參數傳回標量值，傳回的資料是字元類型。
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 ] ) 函數，根據是pathPath 參數，傳回JSON 資料（JSON fragment)；不指定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資料中的屬性值，並傳回修改後的函數修改後的函數是
修改現有的屬性：依照參數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')
五，將JSON資料轉換為關係表
OPENJSON函數是行集函數（RowSet），能夠將JSON資料轉換為關聯表，
OPENJSON( jsonExpression [ , path ] ) [ WITH ( colName type [ column_path ] [ AS JSON ] [ , colName type [ column_path ] [ AS JSON ] ] [ , . . . n ] ) ]
reee
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三列；
示例，从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
)
六，将关系表数据以JSON格式存储
通过For JSON Auto/Path，将关系表数据存储为JSON格式，
Auto 模式：根据select语句中column的顺序，自动生成JSON数据的格式；
Path 模式：使用column name的格式来生成JSON数据的格式，column name使用逗号分隔（dot-separated）表示组-成员关系；
1，以Auto 模式生成JSON格式
select id, name, category from dbo.dt_json for json auto,root('json')
返回的数据格式是
{
"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')
返回的数据格式是：
{
"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"
}
}
]
}
以上就是使用TSQL查询数据和更新JSON数据的内容，更多相关内容请关注PHP中文网（www.php.cn）！
