JSON1：使用TSQL查詢和更新 JSON數據

来源:http://www.cnblogs.com/ljhdo/archive/2016/11/20/4549152.html
-Advertisement-
Play Games

JSON是一個非常流行的，用於數據交換的數據格式，主要用於Web和移動應用程式中。JSON 使用鍵/值對（Key:Value pair）存儲數據，並且表示嵌套鍵值對和數組兩種複雜數據類型，僅僅使用逗號（引用Key）和中括弧（引用數組元素），就能路由到指定的屬性或成員，使用簡單，功能強大。在SQL S ...


JSON是一個非常流行的，用於數據交換的文本數據（textual data）格式，主要用於Web和移動應用程式中。JSON 使用“鍵/值對”（Key:Value pair）存儲數據，能夠表示嵌套鍵值對和數組兩種複雜數據類型，JSON僅僅使用逗號（引用Key）和中括弧（引用數組元素），就能路由到指定的屬性或成員，使用簡單，功能強大。在SQL Server 2016版本中支持JSON格式，使用Unicode字元類型表示JSON數據，並能對JSON數據進行驗證，查詢和修改。推薦一款JSON驗證和格式化的線上工具：json formatter。

SQL Server 提供了內置函數，用於查詢和更新JSON數據，分析JSON文本，如圖：

一，定義和驗證JSON數據

使用nvarchar存儲JSON文本數據，通過函數 ISJSON(expression) 驗證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,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）表示組-成員關係；

示例，有表：dt_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"
      }
   ]
}
View Code

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

七，索引JSON數據

JSON文本不是內置的數據類型，沒有專門的JSON索引，但是，可以通過創建計算列和標準B-Tree索引提高查詢JSON數據的性能，避免全表掃描（Full Table Scan），通過索引計算列，間接實現對JSON進行查找。

索引JSON數據的Workaround是：為查詢條件（Filter）創建計算列，使用persisted屬性持久存儲；在計算列上創建索引，使用包含列（Include）包含特定的欄位，以避免鍵值查找（Key Lookup），提高索引查找的性能。

例如，有如下關係表，欄位category包含JSON數據：

按照type屬性過濾，包含name欄位，創建索引的示例是：

alter table dbo.dt_json
add category_type as (cast(json_value(category,'$.type') as int)) persisted;

create nonclustered index idx_dt_json_category_type
on dbo.dt_json
(
category_type
)
include(name);

 

參考文檔：

JSON Data (SQL Server)

JSON Path Expressions (SQL Server)

JSON Functions (Transact-SQL)

OPENJSON (Transact-SQL)

Index JSON data

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 Support in SQL Server 2016

JSON in SQL Server 2016: Part 1 of 4


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ios 提取html 字元串中的img 的地址（圖片地址）
    本文原文地址 http://www.cnblogs.com/qianLL/p/6082287.html 有時候 後臺返回的是一串html'字元串 我們需要把裡面的圖片地址提取出來 這個關鍵就是一個正確的正則表達式 即 <(img|IMG)(.*?)(/>|></img>|>) 具體代碼如下 返回的是 ...
  • 資料庫SQL server規則的創建、查看、修改和規則的綁定與鬆綁、刪除
    用CREATE RULE語句創建規則 創建雇佣日期規則 hire_date_rule 創建性別規則sex_rule 創建評分規則grade_rule 創建字元規則my_character_rule sp_helptext 查看規則 查看規則hire_date_rule的文本信息 sp_bindrul ...
  • mysql-3 檢索數據（上）
    SELECT 語句 SELECT檢索表數據，必須至少給出兩條信息 想選擇什麼，以及從什麼地方選擇。 檢索一個列 SELECT prod_name FROM products; 上述語句利用SELECT語句從products表中檢索一個名為prod_name的列 檢索多個列 SELECT prod_i ...
  • PostgreSQL模仿Oracle的instr函數
    -- -- instr functions that mimic Oracle's counterpart -- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters. -- -- Searche ...
  • 【函數】Oracle函數系列（2）--數學函數及日期函數
    【函數】Oracle函數系列（2）--數學函數及日期函數 1 BLOG文檔結構圖 2 前言部分 2.1 導讀和註意事項 各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~： ① 數學函數 ② trunc和round函數 ③ 常用日期函數 Tips ...
  • SqlServer 觸發器
    --創建insert類型的觸發器create trigger tgr_product_insert --創建觸發器 on product --所針對的表 for insert --觸發器類型 as --聲明變數 declare @Pid int,@Wid int --獲取添加商品時的主鍵(自增列) ...
  • redis原理分析
    基本全是參考http://blog.csdn.net/a600423444/article/details/8944601 redis的使用大家都很熟悉，可能除了watch 鎖，pipeline，訂閱發佈用的少點，不過網上也有大量的教材和例子，這裡想聊聊redis中的一些原理。 1.redis 提供 ...
  • 數據處理／OLAP／OLTP
    ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類