The Will Will Web

我們都知道 SQL Server 是一套強大的關連式資料庫系統，但從 SQL Server 2016 開始就加入了對 JSON 格式的支援，讓我們可以直接在 SQL Server 透過 T-SQL 就能處理 JSON 資料結構，讓我們有機會在關連式資料庫這種「結構化資料」中加入類似 NoSQL 這種「半結構化」的資料，增加了我們在架構設計與資料處理上的彈性，真的非常方便。這篇文章我就來帶大家快速上手，體驗一下如何在 SQL Server 操作 JSON 資料。

基本上從下圖可以得知 SQL Server 在 JSON 資料結構上的處理能力，大致分成以下幾塊：

1. 解析 JSON 字串，讓你可以讀取、查詢、修改 JSON 的結構
2. 將陣列類型的 JSON 結構轉換成 Table 的格式
3. 透過 T-SQL 操作已經從 JSON 轉換成 Table 的格式的資料
4. 將 T-SQL 的查詢結果轉回 JSON 格式輸出

解析 JSON 字串，讓你可以讀取、查詢、修改 JSON 的結構

1. 使用 ISJSON (Transact-SQL) 判斷文字是否為合法的 JSON 格式

   DECLARE @json NVARCHAR(MAX);
   
   SET @json = N'[
     {"id": 1, "info": {"name": "Will", "surname": "Huang"}, "age": 25},
     {"id": 2, "info": {"name": "Peter", "surname": "Liou"}, "age": 28}
   ]';
   
   SELECT ISJSON(@json) as [IsJson] -- 1
   

2. 使用 JSON_VALUE (Transact-SQL) 擷取 JSON 中的資料(Scalar value)

   DECLARE @json NVARCHAR(MAX);
   
   SET @json = N'{"id": 1, "info": {"name": "Will", "surname": "Huang"}, "age": 25}';
   
   SELECT JSON_VALUE(@json, '$.info.name') as [Name] -- Will
   

3. 使用 JSON_QUERY (Transact-SQL) 擷取 JSON 中的物件或陣列

   DECLARE @json NVARCHAR(MAX);
   
   SET @json = N'{"id": 1, "info": {"name": "Will", "surname": "Huang"}, "age": 25}';
   
   SELECT JSON_QUERY(@json, '$.info') as [Info] -- {"name": "Will", "surname": "Huang"}
   

4. 使用 JSON_MODIFY (Transact-SQL) 變更 JSON 字串中的內容

   DECLARE @json NVARCHAR(MAX);
   
   SET @json = N'{"id": 1, "info": {"name": "Will", "surname": "Huang"}, "age": 25}';
   
   SET @json = JSON_MODIFY(@json, '$.info.surname', 'Lee');
   
   SELECT modifiedJson = @json -- {"id": 1, "info": {"name": "Will", "surname": "Lee"}, "age": 25}
   

將陣列類型的 JSON 結構轉換成 Table 的格式

1. 使用 OPENJSON (Transact-SQL) 將 JSON 轉換成 Table

   如果單純的透過 SELECT * FROM OPENJSON(@json) 取出資料，會得到 key, value 與 type 的資訊：

   DECLARE @json NVARCHAR(MAX);
   
   SET @json = N'[
       {"id": 1, "info": {"name": "Will", "surname": "Huang"}, "age": 25},
       {"id": 2, "info": {"name": "Peter", "surname": "Liou"}, "age": 28}
   ]';
   
   SELECT * FROM OPENJSON(@json)
   

   

   關於 type 所代表的意思，可以參考 jsonExpression 文件。

2. 使用 OPENJSON (Transact-SQL) 將 JSON 轉換成 Table

   將 JSON 中的資料投射成表格的樣子，加上 WITH ( ... ) 就可以了，你可以很輕鬆的將一個多層次巢狀的 JSON 結構轉換成扁平的 Table 表格結構：

   DECLARE @json NVARCHAR(MAX);
   
   SET @json = N'[
       {"id": 1, "info": {"name": "Will", "surname": "Huang"}, "age": 25},
       {"id": 2, "info": {"name": "Peter", "surname": "Liou"}, "age": 28}
   ]';
   
   SELECT * FROM OPENJSON(@json)
     WITH (
       id INT 'strict $.id',
       firstName NVARCHAR(50) '$.info.name',
       lastName NVARCHAR(50) '$.info.surname',
       age INT
     );
   

   

透過 T-SQL 操作已經從 JSON 轉換成 Table 的格式的資料

你只要能透過 OPENJSON (Transact-SQL) 將 JSON 轉成 Table，剩下的就沒有什麼不可能的事了，你就放心的直接將這些 Table 當成一般 Table 來操作即可，該 JOIN 的時候 JOIN，該 GROUP BY 的時候 GROUP BY，想用 ORDER BY 的時候也請不用客氣，太方便了！👍

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
    {"id": 1, "info": {"name": "Will", "surname": "Huang"}, "age": 25},
    {"id": 2, "info": {"name": "Peter", "surname": "Liou"}, "age": 28}
]';

SELECT *
FROM OPENJSON(@json)
  WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT
  )
WHERE firstName LIKE 'W%'
ORDER BY id DESC;

將 T-SQL 的查詢結果轉回 JSON 格式輸出

事實上，你可以將任意 T-SQL 的查詢結果轉換成 JSON 結構輸出！

1. 將 JSON 轉 Table 後，換成另一種 JSON 結構輸出

   DECLARE @json NVARCHAR(MAX);
   
   SET @json = N'[
       {"id": 1, "info": {"name": "Will", "surname": "Huang"}, "age": 25},
       {"id": 2, "info": {"name": "Peter", "surname": "Liou"}, "age": 28}
   ]';
   
   SELECT id, firstName AS "name.firstName", lastName AS "name.lastName", age
   FROM OPENJSON(@json)
     WITH (
       id INT 'strict $.id',
       firstName NVARCHAR(50) '$.info.name',
       lastName NVARCHAR(50) '$.info.surname',
       age INT
     )
   FOR JSON PATH;
   

   上面的 T-SQL 將輸出以下 JSON 資料：

   [
       {
           "id": 1,
           "name": {
               "firstName": "Will",
               "lastName": "Huang"
           },
           "age": 25
       },
       {
           "id": 2,
           "name": {
               "firstName": "Peter",
               "lastName": "Liou"
           },
           "age": 28
       }
   ]
   

   關於 FOR JSON PATH 語句的技術細節可以參考 Format Query Results as JSON with FOR JSON (SQL Server) 與 SELECT - FOR Clause (Transact-SQL) 文件。

2. 如果 JSON 第一層不希望直接以「陣列」開頭

   假設你想要輸出一個類似以下的 JSON 結構：

   {
     "Results": [
       ...
     ]
   }
   

   那麼你可以這樣寫：

   DECLARE @json NVARCHAR(MAX);
   
   SET @json = N'[
       {"id": 1, "info": {"name": "Will", "surname": "Huang"}, "age": 25},
       {"id": 2, "info": {"name": "Peter", "surname": "Liou"}, "age": 28}
   ]';
   
   SELECT id, firstName AS "name.firstName", lastName AS "name.lastName", age
   FROM OPENJSON(@json)
     WITH (
       id INT 'strict $.id',
       firstName NVARCHAR(50) '$.info.name',
       lastName NVARCHAR(50) '$.info.surname',
       age INT
     )
   FOR JSON PATH, ROOT('Results');
   

   更多 JSON 輸出的技巧可參見 Format Nested JSON Output with PATH Mode (SQL Server) 說明。

3. 如果你希望每一筆 [SalesLT].[Product] 回傳的資料都轉成 JSON 格式，可以這樣寫：

   以下是來自 AdventureWorks 範例資料庫的 [SalesLT].[Product] 資料表：

   SELECT TOP (3)
     (SELECT ProductID, Name, ProductNumber FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) as value
   FROM [SalesLT].[Product]
   

   

應用情境

我在官網文件的 Use cases for JSON data in SQL Server 看到了幾個典型的應用情境，我覺得大家可以透過這些情境去聯想有沒有什麼是你也可以用上的！

• 簡化複雜的資料模型 (Simplify complex data models)

  考慮對你的資料模型進行「反正規化」設計，使用 JSON 屬性來儲存多重「子表」的設計。

• 儲存零售與電子商務資料 (Store retail and e-commerce data)

  許多零售與電子商務資料很難建模(Modeling)，因為不同商品之間的屬性差異甚大，非常難「正規化」處理，硬要正規化處理就會讓資料結構(Schema)變的異常複雜，也失去了很多開發上的彈性。

• 處理記錄與遙測資料 (Process log and telemetry data)

  載入、查詢、分析記錄資料，使用 JSON 儲存還能搭配 T-SQL 的強大查詢能力，可以大幅簡化應用程式架構。

• 儲存半結構化的 IoT 數據 (Store semi-structured IoT data)

  當你需要即時分析 IoT 數據時，直接將傳入的資料寫入資料庫會比先儲存在檔案系統簡便的多！

• 簡化 REST API 開發 (Simplify REST API development)

  直接將關連式資料庫查詢到的資料轉換成 JSON 結構，應用程式的複雜度將可以大幅降低複雜度。

  影片: Building REST API with SQL Server using JSON functions

總結

任何以 VARCHAR 或 NVARCHAR 欄位都可以使用 SQL Server 中與 JSON 相關的函式來操作，也可以把 JSON 儲存在記憶體中或暫存資料表中，這部分真的相當厲害。

事實上 SQL Server 的 JSON 還有很多功能，例如怎樣儲存 JSON 資料到資料庫、怎樣替 JSON 建立索引、怎樣在 in-memory OLTP 情境下最佳化 JSON 處理、怎樣將大量 JSON 檔案匯入 SQL Server (PolyBase)，這些都是相當有趣的主題。

如果你想直接從 SQL Server 的表格中，直接把儲存 JSON 資料的欄位取出，透過 WHERE 篩選或取出資料產生報表，以下 T-SQL 就是一個很好的範例，這種結合 T-SQL 與 JSON 的作法真的很強大！ 👍

SELECT Tab.Id, SalesOrderJsonData.Customer, SalesOrderJsonData.Date
FROM SalesOrderRecord AS Tab
CROSS APPLY OPENJSON (Tab.json, N'$.Orders.OrdersArray')
  WITH (
    Number VARCHAR(200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR(200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
  ) AS SalesOrderJsonData
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'
ORDER BY JSON_VALUE(Tab.json, '$.Group'), Tab.DateModified;

Analyze JSON data with SQL queries

相關連結

• 使用 JSON 資料 - SQL Server | Microsoft Learn
• OPENJSON (Transact-SQL) - SQL Server | Microsoft Learn
• How FOR JSON converts SQL Server data types to JSON data types (SQL Server)
• JSON as a bridge betwen NoSQL and relational worlds (Video)