【SqlServer系列】JSON數據_ZenDei技術網路在線

1 概述 1 概述本文將結合MSDN簡要概述JSON數據。 2 具體內容 2 具體內容 JSON 是一種流行的數據格式，用於在現代 Web 和移動應用程式中交換數據。 JSON 還可用於在 Microsoft Azure DocumentDB 等 NoSQL 資料庫中存儲非結構化數據。許多 RE ...

1 概述

本文將結合MSDN簡要概述JSON數據。

2 具體內容

JSON 是一種流行的數據格式，用於在現代 Web 和移動應用程式中交換數據。 JSON 還可用於在 Microsoft Azure DocumentDB 等 NoSQL 資料庫中存儲非結構化數據。許多 REST Web 服務以 JSON 文本格式返回結果，或接受採用 JSON 格式的數據。例如，大多數 Azure 服務（如 Azure 搜索、Azure 存儲和 Azure DocumentDb）都提供返回或使用 JSON 的 REST 終結點。 JSON 也是用於通過 AJAX 調用在網頁與 Web 伺服器之間交換數據的主要格式。

2.1 將 JSON 集合轉換為行集

 1 DECLARE @json NVARCHAR(MAX)
 2 SET @json =  
 3 N'[  
 4        { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
 5        { "id" : 5,"info": { "name": "Jane", "surname": "Smith" }, "dob": "2005-11-04T12:00:00" }  
 6  ]'  
 7 
 8 SELECT *  
 9 FROM OPENJSON(@json)  
10   WITH (id int 'strict $.id',  
11         firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
12         age int, dateOfBirth datetime2 '$.dob')

2.2 將 SQL Server 數據轉換為 JSON 或導出 JSON

通過將 FOR JSON 子句添加到 SELECT 語句中，可將 SQL Server 數據或 SQL 查詢結果的格式設置為 JSON。使用 FOR JSON 委托從客戶端應用程式到 SQL Server 的 JSON 輸出格式。有關詳細信息，請參閱藉助 FOR JSON 將查詢結果的格式設置為 JSON (SQL Server)。

以下示例使用 PATH 模式和 FOR JSON 子句。

1 SELECT id, firstName AS "info.name", lastName AS "info.surname", age, dateOfBirth as dob  
2 FROM People  
3 FOR JSON PATH

“應用程式池:” FOR JSON 子句將 SQL 結果的格式設置為 JSON 文本，該格式可提供給識別 JSON 的任何應用。 PATH 選項在 SELECT 子句中使用以點分隔的別名，以嵌套查詢結果中的對象。

2.3 合併關係數據和 JSON 數據

SQL Server 提供混合模型，用於通過標準 Transact-SQL 語言存儲和處理關係數據與 JSON 數據。可以將 JSON 文檔的集合組織到表中，在它們之間建立關係，將表中存儲的強類型標量列與 JSON 列中存儲的靈活鍵/值對合併，以及使用完整 Transact SQL 查詢一個或多個表中的標量值和 JSON 值。

JSON 文本通常存儲在 varchar 或 nvarchar 列中，並編製了純文本形式的索引。任何支持文本的 SQL Server 功能或組件均支持 JSON，因此 JSON 和其他 SQL Server 功能之間的交互幾乎沒有任何約束。你可以將 JSON 存儲在記憶體中或臨時表中、對 JSON 文本應用行級別安全性謂詞等。

如果在單純的 JSON 工作負載中，你想要使用專用於處理 JSON 文檔的自定義查詢語言，可以考慮 Microsoft Azure DocumentDB。

以下用例說明如何在 SQL Server中使用內置的 JSON 支持。

2.4 從格式化為 JSON 的 SQL Server 表返回數據

如果你的 Web 服務從資料庫層提取數據並以 JSON 格式返回數據，或者在接受已格式化為 JSON 的數據的 JavaScript 框架或庫中返回數據，則可以直接在 SQL 查詢中設置 JSON 輸出的格式。你可以使用 FOR JSON 將 JSON 格式設置委托給 SQL Server，而非編寫代碼或者包含一個庫來轉換表格查詢結果對象，然後將對象序列化為 JSON 格式。

例如，你可能想要生成符合 OData 規範的 JSON 輸出。 Web 服務需要採用以下格式的請求和響應。

請求： /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName
響應： {"@odata.context":"http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity","ProductID":1,"ProductName":"Chai"}

此 OData URL 代表針對 ID 為 1 的產品的 ProductID 和 ProductName 列的請求。可以使用 FOR JSON 按 SQL Server 中所需的格式設置輸出格式。

1 SELECT 'http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity'
2  AS '@odata.context',   
3  ProductID, Name as ProductName   
4 FROM Production.Product  
5 WHERE ProductID = 1  
6 FOR JSON AUTO

此查詢的輸出是完全符合 OData 規範的 JSON 文本。格式設置和轉義由 SQL Server 處理。 SQL Server 還可將查詢結果的格式設置為任何格式，如 OData JSON 或 GeoJSON - 有關詳細信息，請參閱 Returning spatial data in GeoJSON format（以 GeoJSON 格式返回空間數據）。

2.5 使用 SQL 查詢分析 JSON 數據

如果必須篩選或聚合 JSON 數據以用於報告，可以使用 OPENJSON 將 JSON 轉換為關係格式。然後，使用標準 Transact-SQL 和內置函數來準備報告。

 1 SELECT Tab.Id, SalesOrderJsonData.Customer, SalesOrderJsonData.Date  
 2 FROM   SalesOrderRecord AS Tab  
 3           CROSS APPLY  
 4      OPENJSON (Tab.json, N'$.Orders.OrdersArray')  
 5            WITH (  
 6               Number   varchar(200) N'$.Order.Number',   
 7               Date     datetime     N'$.Order.Date',  
 8               Customer varchar(200) N'$.AccountNumber',   
 9               Quantity int          N'$.Item.Quantity'  
10            )  
11   AS SalesOrderJsonData  
12 WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'  
13 ORDER BY JSON_VALUE(Tab.json, '$.Group'), Tab.DateModified

可以在同一個查詢中使用標準表列和來自 JSON 文本的值。可以在 JSON_VALUE(Tab.json, '$.Status') 表達式上添加索引以提高查詢的性能。有關詳細信息，請參閱對 JSON 數據編製索引。

2.6 將 JSON 數據導入 SQL Server 表

如果必須將 JSON 數據從外部服務載入到 SQL Server，則可以使用 OPENJSON 將數據導入 SQL Server，而非分析應用程式層中的數據。

 1 DECLARE @jsonVariable NVARCHAR(MAX)
 2 
 3 SET @jsonVariable = N'[  
 4         {  
 5           "Order": {  
 6             "Number":"SO43659",  
 7             "Date":"2011-05-31T00:00:00"  
 8           },  
 9           "AccountNumber":"AW29825",  
10           "Item": {  
11             "Price":2024.9940,  
12             "Quantity":1  
13           }  
14         },  
15         {  
16           "Order": {  
17             "Number":"SO43661",  
18             "Date":"2011-06-01T00:00:00"  
19           },  
20           "AccountNumber":"AW73565",  
21           "Item": {  
22             "Price":2024.9940,  
23             "Quantity":3  
24           }  
25        }  
26   ]'
27 
28 INSERT INTO SalesReport  
29 SELECT SalesOrderJsonData.*  
30 FROM OPENJSON (@jsonVariable, N'$.Orders.OrdersArray')  
31            WITH (  
32               Number   varchar(200) N'$.Order.Number',   
33               Date     datetime     N'$.Order.Date',  
34               Customer varchar(200) N'$.AccountNumber',   
35               Quantity int          N'$.Item.Quantity'  
36            )  
37   AS SalesOrderJsonData;

外部 REST 服務可以提供 JSON 變數的內容，這些內容將從客戶端 JavaScript 框架作為參數發送，或者從外部文件載入。你可以在 SQL Server 表中輕鬆插入、更新或合併來自 JSON 文本的結果。有關此方案的詳細信息，請參閱以下博客文章。

2.7 將 JSON 文件載入到 SQL Server

文件中存儲的信息可格式化為標準 JSON 或行分隔的 JSON。 SQL Server 可以導入 JSON 文件的內容，使用 OPENJSON 或 JSON_VALUE 函數分析內容，並將其載入到表中。

如果 JSON 文檔存儲在可由 SQL Server 訪問的本地文件、共用網路驅動器或 Azure 文件存儲位置，可以使用批量導入將 JSON 數據載入到 SQL Server。有關此方案的詳細信息，請參閱 Importing JSON files into SQL Server using OPENROWSET (BULK)（使用 OPENROWSET (BULK) 將 JSON 文件導入 SQL Server）。
如果行分隔的 JSON 文件存儲在 Azure Blob 存儲或 Hadoop 文件系統中，你可以使用 Polybase 來載入 JSON 文本，在 Transact-SQL 代碼中分析文本，然後將其載入表中。

2.8 測試驅動內置的 JSON 支持

使用 AdventureWorks 示例資料庫測試驅動內置 JSON 支持。若要獲取 AdventureWorks 示例資料庫，必須從此處。將示例資料庫還原到 SQL Server 2016 實例後，請解壓縮示例文件，然後從 JSON 文件夾中打開“JSON Sample Queries procedures views and indexes.sql”文件。運行此文件中的腳本，將某些現有數據的格式重新設置為 JSON 數據，對 JSON 數據運行示例查詢和報告，為 JSON 數據編製索引，然後導入和導出 JSON。

下麵是你可以對該文件中包含的腳本執行的操作。

使現有架構非規範化以創建 JSON 數據的列。
1. 將 SalesReasons、SalesOrderDetails、SalesPerson、Customer 和包含銷售訂單相關信息的表中的信息存儲到 SalesOrder_json 表的 JSON 列中。
2. 將 EmailAddresses/PersonPhone 表中的信息作為 JSON 對象的數組存儲到 Person_json 表中。
創建查詢 JSON 數據的過程和視圖。
為 JSON 數據編製索引 – 為 JSON 屬性和全文索引創建索引。
導入和導出 JSON – 創建並運行以 JSON 結果形式導出 Person 和 SalesOrder 表內容，並使用 JSON 輸入導入和更新 Person 與 SalesOrder 表的過程。
運行查詢示例 – 運行一些查詢，用於調用步驟 2 和 4 中創建的存儲過程與視圖。
清理腳本 – 如果你想要保留步驟 2 和 4 中創建的存儲過程與視圖，請不要運行此部件。

3 參考文獻

【01】https://docs.microsoft.com/zh-cn/sql/relational-databases/json/json-data-sql-server

4 版權

感謝您的閱讀，若有不足之處，歡迎指教，共同學習、共同進步。
博主網址：http://www.cnblogs.com/wangjiming/。
極少部分文章利用讀書、參考、引用、抄襲、複製和粘貼等多種方式整合而成的，大部分為原創。
如您喜歡，麻煩推薦一下；如您有新想法，歡迎提出，郵箱：[email protected]。
可以轉載該博客，但必須著名博客來源。