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 數據,通過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 ] ) ]
示例,從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格式,
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" } } ] }
以上就是本文的全部內容,希望對大家的學習有所幫助,也希望大家多多支持。