# 快速上手 SQL Server 的 JSON 操作能力

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

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

![SQL Server 在 JSON 資料結構上的處理能力](https://stwillblogassets.blob.core.windows.net/files/images/external/user-images.githubusercontent.com/9fac8e5f6d2e66cca619-210955952-87db7926-130b-4105-a81f-5cdd03986873.png)

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

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

1.  使用 [ISJSON (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/isjson-transact-sql?view=sql-server-ver16) 判斷文字是否為合法的 JSON 格式
    
    ```sql
    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)](https://learn.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver16) 擷取 JSON 中的資料(Scalar value)
    
    ```sql
    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)](https://learn.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql?view=sql-server-ver16) 擷取 JSON 中的物件或陣列
    
    ```sql
    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)](https://learn.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql?view=sql-server-ver16) 變更 JSON 字串中的內容
    
    ```sql
    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)](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) 將 JSON 轉換成 Table
    
    如果單純的透過 `SELECT * FROM OPENJSON(@json)` 取出資料，會得到 `key`, `value` 與 `type` 的資訊：
    
    ```sql
    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)
    ```
    
    ![image](https://stwillblogassets.blob.core.windows.net/files/images/external/user-images.githubusercontent.com/5492e7898d774a86c845-210965720-4c805a28-8d55-4cc4-ada0-75e7f3f80bad.png)
    
    > 關於 `type` 所代表的意思，可以參考 [jsonExpression](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686#jsonexpression) 文件。
    
2.  使用 [OPENJSON (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) 將 JSON 轉換成 Table
    
    將 JSON 中的資料投射成表格的樣子，加上 `WITH ( ... )` 就可以了，你可以很輕鬆的將一個多層次巢狀的 JSON 結構轉換成扁平的 Table 表格結構：
    
    ```sql
    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
      );
    ```
    
    ![image](https://stwillblogassets.blob.core.windows.net/files/images/external/user-images.githubusercontent.com/79177c826cb81295d712-210965946-e6899701-ae71-4a57-9503-7b6d33be74ab.png)
    

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

你只要能透過 [OPENJSON (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) 將 JSON 轉成 Table，剩下的就沒有什麼不可能的事了，你就放心的直接將這些 Table 當成一般 Table 來操作即可，該 JOIN 的時候 JOIN，該 GROUP BY 的時候 GROUP BY，想用 ORDER BY 的時候也請不用客氣，太方便了！👍

```sql
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 結構輸出
    
    ```sql
    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 資料：
    
    ```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)](https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) 與 [SELECT - FOR Clause (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-for-clause-transact-sql?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) 文件。
    
2.  如果 JSON 第一層不希望直接以「陣列」開頭
    
    假設你想要輸出一個類似以下的 JSON 結構：
    
    ```json
    {
      "Results": [
        ...
      ]
    }
    ```
    
    那麼你可以這樣寫：
    
    ```sql
    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)](https://learn.microsoft.com/en-us/sql/relational-databases/json/format-nested-json-output-with-path-mode-sql-server?view=sql-server-ver16) 說明。
    
3.  如果你希望每一筆 `[SalesLT].[Product]` 回傳的資料都轉成 JSON 格式，可以這樣寫：
    
    以下是來自 [AdventureWorks 範例資料庫](https://learn.microsoft.com/zh-tw/sql/samples/adventureworks-install-configure?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686&tabs=ssms)的 `[SalesLT].[Product]` 資料表：
    
    ```sql
    SELECT TOP (3)
      (SELECT ProductID, Name, ProductNumber FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) as value
    FROM [SalesLT].[Product]
    ```
    
    ![WITHOUT\_ARRAY\_WRAPPER](https://stwillblogassets.blob.core.windows.net/files/images/external/user-images.githubusercontent.com/ac1f9f6a2875524a5903-211233394-7f1b44ec-8cbc-4bdf-9b4e-07eefb2743d1.png)
    

### 應用情境

我在官網文件的 [Use cases for JSON data in SQL Server](https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686#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](https://www.youtube.com/watch?v=0m6GXF3-5WI)
    

### 總結

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

事實上 SQL Server 的 JSON 還有很多功能，例如[怎樣儲存 JSON 資料到資料庫](https://learn.microsoft.com/en-us/sql/relational-databases/json/store-json-documents-in-sql-tables?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)、[怎樣替 JSON 建立索引](https://learn.microsoft.com/en-us/sql/relational-databases/json/index-json-data?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)、[怎樣在 in-memory OLTP 情境下最佳化 JSON 處理](https://learn.microsoft.com/en-us/sql/relational-databases/json/optimize-json-processing-with-in-memory-oltp?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)、[怎樣將大量 JSON 檔案匯入 SQL Server](https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686#load-json-files-into-sql-server) ([PolyBase](https://learn.microsoft.com/zh-tw/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686))，這些都是相當有趣的主題。

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

```sql
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](https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686#analyze-json-data-with-sql-queries)

### 相關連結

-   [使用 JSON 資料 - SQL Server | Microsoft Learn](https://learn.microsoft.com/zh-tw/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)
-   [OPENJSON (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)
-   [How FOR JSON converts SQL Server data types to JSON data types (SQL Server)](https://learn.microsoft.com/en-us/sql/relational-databases/json/how-for-json-converts-sql-server-data-types-to-json-data-types-sql-server?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)
-   [JSON as a bridge betwen NoSQL and relational worlds](https://learn.microsoft.com/en-us/events/datadriven-sqlserver2016/json-as-bridge-betwen-nosql-relational-worlds?WT.mc_id=DT-MVP-4015686) (Video)