JSON1：JSON資料的操作

標籤：tar   new   odi   contract   basic   選項   text   第一個   預設值   

JSON是一個非常流行的，用於資料交換的資料格式，主要用於Web和行動裝置 App程式中。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 ]         )  ] 

View Code

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

View Code

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

View Code

 

參考文檔：

JSON Path Expressions (SQL Server)

JSON Functions (Transact-SQL)

OPENJSON (Transact-SQL)

Format Query Results as JSON with FOR JSON (SQL Server)

Format Nested JSON Output with PATH Mode (SQL Server)

Format JSON Output Automatically with AUTO Mode (SQL Server)

JSON Data (SQL Server)

JSON Support in SQL Server 2016

JSON in SQL Server 2016: Part 1 of 4

JSON1：JSON資料的操作