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,tagsFROM 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, categoryfrom dbo.dt_jsonfor 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_jsonfor 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"}}]}

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