DIV CSS 佈局教程網

 DIV+CSS佈局教程網 >> 網頁腳本 >> JavaScript入門知識 >> JSON基礎 >> 詳解JSON1:使用TSQL查詢數據和更新JSON數據
詳解JSON1:使用TSQL查詢數據和更新JSON數據
編輯:JSON基礎      日期:2017/4/13 9:50:19

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 ] ) 函數,根據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數據的流程是:

  • 修改現有的屬性:按照參數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 ] 
  ) 
]
  • 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和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"
}
}
]
}

以上就是本文的全部內容,希望對大家的學習有所幫助,也希望大家多多支持。

XML學習教程| jQuery入門知識| AJAX入門| Dreamweaver教程| Fireworks入門知識| SEO技巧| SEO優化集錦|
Copyright © DIV+CSS佈局教程網 All Rights Reserved