處理內建函式的 JSON 資料

12 分鐘

小提示

有關更多詳細信息，請參閱 文本和圖像 選項卡！

試想一個情境，你的電子商務應用程式將顧客偏好和訂單中繼資料儲存為 JSON 文件。行動應用程式會以 JSON 格式傳送購物車資料，而你的報告系統需要將產品目錄匯出成 JSON 格式，供網路 API 使用。直接在資料庫中使用 JSON 可以省去應用層轉換的需求，並提升資料處理效率。

Fabric 中的 SQL Server、Azure SQL 和 SQL 資料庫提供內建的 JSON 支援，讓你能直接在 T-SQL 中解析、查詢、建立並轉換 JSON 資料。在這個單元中，你會學習如何使用 JSON 函式來萃取數值、構建 JSON 輸出、將資料彙整成 JSON 陣列，以及驗證 JSON 內容。

用 JSON_VALUE 和 JSON_QUERY 提取數值

當你處理資料庫中儲存的 JSON 時，你需要擷取特定值來篩選、加入或顯示。 SQL Server 提供兩個功能以達成此目的：

JSON_VALUE() 從 JSON 字串中擷取一個純量值（字串、數字、布林值）：

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd",
        "active": true
    },
    "orderTotal": 1599.99
}';

SELECT 
    JSON_VALUE(@json, '$.customer.id') AS CustomerID,
    JSON_VALUE(@json, '$.customer.name') AS CustomerName,
    JSON_VALUE(@json, '$.orderTotal') AS OrderTotal;

結果集將為：

CustomerID   CustomerName   OrderTotal
----------   ------------   ----------
12345        Contoso Ltd    1599.99

函式會利用路徑運算式來導航 JSON 結構，並將值 NVARCHAR(4000) 以字串形式回傳。你可以根據需要將結果投射到其他資料型態，以便計算或比較。

JSON_QUERY() 擷取一個 JSON 物件或陣列（非純量值）：

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd"
    },
    "items": [
        {"product": "Widget", "qty": 5},
        {"product": "Gadget", "qty": 3}
    ]
}';

SELECT 
    JSON_QUERY(@json, '$.customer') AS CustomerObject,
    JSON_QUERY(@json, '$.items') AS ItemsArray;

結果集將為：

CustomerObject                          ItemsArray
--------------------------------------  ------------------------------------------------
{"id": 12345,"name": "Contoso Ltd"}     [{"product": "Widget", "qty": 5},{"product": "Gadget", "qty": 3}]

與不同 JSON_VALUE()， JSON_QUERY() 保留了 JSON 結構，將物件和陣列以有效的 JSON 字串形式回傳，供你儲存、傳遞給其他函式或應用程式。

路徑表達式用 $ 來表示根元素，巢狀屬性用點符號表示，陣列元素用括號表示，如下範例所示：

-- Access array elements by index (0-based)
SELECT JSON_VALUE(@json, '$.items[0].product') AS FirstProduct;

結果將是：

FirstProduct
------------
Widget

陣列索引從 0 開始，因此 $.items[0] 指的是第一個元素。使用此語法來提取特定項目（當你知道它們的位置時），或結合 OPENJSON 以處理所有陣列元素。

小提示

當你需要純量值來比較或計算時才會用 JSON_VALUE() 。當你需要保留巢狀物件或陣列的 JSON 結構時，才會用 JSON_QUERY() 它。

使用 `OPENJSON` 解析 JSON 陣列

OPENJSON 是一個將 JSON 資料轉換成關聯式列集的表值函式。使用此函式將 JSON 資料與關聯式資料表結合，或單獨處理陣列元素。

以下查詢將 JSON 陣列解析為預設結構的列：

DECLARE @json NVARCHAR(MAX) = N'[
    {"id": 1, "name": "Widget", "price": 29.99},
    {"id": 2, "name": "Gadget", "price": 49.99},
    {"id": 3, "name": "Gizmo", "price": 19.99}
]';

SELECT * FROM OPENJSON(@json);

結果集將為：

key   value                                          type
---   --------------------------------------------   ----
0     {"id": 1, "name": "Widget", "price": 29.99}   5
1     {"id": 2, "name": "Gadget", "price": 49.99}   5
2     {"id": 3, "name": "Gizmo", "price": 19.99}    5

若無結構，則 OPENJSON 回傳三欄： key （陣列索引或屬性名稱）、 value （JSON 內容）及 type （表示 JSON 資料型別的數字：0=空，1=字串，2=數字，3=布林，4=陣列，5=物件）。

以下查詢定義一個明確的結構結構，以擷取具有適當資料型別的特定欄位：

SELECT 
    ProductID,
    ProductName,
    Price
FROM OPENJSON(@json)
WITH (
    ProductID INT '$.id',
    ProductName NVARCHAR(100) '$.name',
    Price DECIMAL(10,2) '$.price'
);

結果集將為：

ProductID   ProductName   Price
---------   -----------   ------
1           Widget        29.99
2           Gadget        49.99
3           Gizmo         19.99

此 WITH 子句將 JSON 屬性映射到已類型化的欄位。這種方法能提供適當的計算與比較資料型態，並允許你只選擇所需的屬性。

將 OPENJSON 與表格資料結合使用 CROSS APPLY：

-- Assuming Orders table has a JSON column called OrderDetails
SELECT 
    o.OrderID,
    o.CustomerID,
    items.ProductName,
    items.Quantity,
    items.UnitPrice
FROM Orders AS o
CROSS APPLY OPENJSON(o.OrderDetails)
WITH (
    ProductName NVARCHAR(100) '$.product',
    Quantity INT '$.qty',
    UnitPrice DECIMAL(10,2) '$.price'
) AS items;

備註

當程式碼將 OPENJSON 與 CROSS APPLY 搭配使用時，主表中具有 NULL 或 JSON 值為空的資料列不會出現在結果中。如果你需要包含沒有 JSON 資料的列，就用它 OUTER APPLY 。

構造包含`JSON_OBJECT` 和 `JSON_ARRAY` 的 JSON

SQL Server 2022 引入了用於直覺地建構 JSON 的 JSON_OBJECT 和 JSON_ARRAY 函式：

JSON_OBJECT() 由鍵值對建立 JSON 物件。以下範例展示了如何為產品建立 JSON 物件：

SELECT JSON_OBJECT(
    'id': ProductID,
    'name': Name,
    'price': ListPrice,
    'available': CASE WHEN SellEndDate IS NULL THEN 'true' ELSE 'false' END
) AS ProductJson
FROM SalesLT.Product
WHERE ProductID = 680;

結果將是：

ProductJson
---------------------------------------------------------------------------
{"id":680,"name":"HL Road Frame - Black, 58","price":1431.50,"available":"true"}

該函式會自動處理資料類型轉換及字串值中特殊字元的正確 JSON 逸出。

JSON_ARRAY() 從值建立一個 JSON 陣列。以下範例是建立一個 JSON 陣列：

SELECT JSON_ARRAY(
    'SQL Server',
    'Azure SQL Database',
    'SQL Database in Fabric'
) AS Platforms;

結果將是：

Platforms
---------------------------------------------------------
["SQL Server","Azure SQL Database","SQL Database in Fabric"]

你可以將欄位值、變數或字面值傳給 JSON_ARRAY()。這個函式無論輸入類型如何，都能建立一個格式正確的 JSON 陣列。

接著，將這些函式結合起來，建立巢狀的 JSON 結構。以下範例構建了一個包含客戶與總額資訊的完整訂單 JSON 物件：

SELECT JSON_OBJECT(
    'orderId': soh.SalesOrderID,
    'orderDate': soh.OrderDate,
    'customer': JSON_OBJECT(
        'id': c.CustomerID,
        'name': c.CompanyName
    ),
    'totals': JSON_OBJECT(
        'subtotal': soh.SubTotal,
        'tax': soh.TaxAmt,
        'total': soh.TotalDue
    )
) AS OrderJson
FROM SalesLT.SalesOrderHeader AS soh
INNER JOIN SalesLT.Customer AS c
    ON soh.CustomerID = c.CustomerID
WHERE soh.SalesOrderID = 71774;

結果將是：

OrderJson
--------------------------------------------------------------------------------
{"orderId":71774,"orderDate":"2008-06-01","customer":{"id":29825,"name":"Contoso"},"totals":{"subtotal":880.35,"tax":70.43,"total":972.79}}

巢狀呼叫 JSON_OBJECT，可以建立符合應用程式預期格式的階層式結構。此方法比字串串接更乾淨，並確保 JSON 輸出有效。

彙整資料 `JSON_ARRAYAGG`

JSON_ARRAYAGG 將多列的值聚合成單一 JSON 陣列。此函式有助於從正規化關聯資料產生非正規化的 JSON 輸出：

SELECT 
    c.CustomerID,
    c.CompanyName,
    JSON_ARRAYAGG(soh.SalesOrderID) AS OrderIds
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
    ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID, c.CompanyName;

結果將是：

CustomerID   CompanyName           OrderIds
----------   -------------------   ------------------
29825        Contoso Retail        [71774,71776,71780]
29847        Adventure Works       [71782,71784]

這個函式會從分組的列中收集所有相符的值，並將它們合併成一個 JSON 陣列。此方法有助於從正規化資料庫資料表中建立非正規化的 API 回應。

你可以將 JSON_ARRAYAGG 和 JSON_OBJECT 結合起來，建立複雜物件的陣列：

SELECT 
    pc.Name AS Category,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id': p.ProductID,
            'name': p.Name,
            'price': p.ListPrice
        )
    ) AS Products
FROM SalesLT.ProductCategory AS pc
INNER JOIN SalesLT.Product AS p
    ON pc.ProductCategoryID = p.ProductCategoryID
GROUP BY pc.ProductCategoryID, pc.Name;

結果如下：

Category        Products
--------------  --------------------------------------------------------------------------
Road Bikes      [{"id":749,"name":"Road-150 Red, 62","price":3578.27},{"id":750,"name":"Road-150 Red, 44","price":3578.27}]
Mountain Bikes  [{"id":771,"name":"Mountain-100 Silver, 38","price":3399.99},{"id":772,"name":"Mountain-100 Black, 38","price":3374.99}]

這很重要

JSON_ARRAYAGG 和 JSON_OBJECT/JSON_ARRAY 函式可在 SQL Server 2022 及以後版本、Azure SQL 資料庫和 Microsoft Fabric 的 SQL 資料庫中使用。對於早期版本，請使用 FOR JSON PATH 以實現類似的功能。

使用`JSON_CONTAINS` 驗證並檢查 JSON

來自外部來源的 JSON 資料可能格式不佳、缺少預期屬性，或包含意外值。嘗試從無效的 JSON 或缺失路徑中擷取數值，可能導致查詢失敗或回傳誤導 NULL 性的結果，掩蓋資料問題。

穩健的 JSON 處理需要防禦性編碼：在解析前先驗證 JSON 是否正確。然後在提取數值前，先確認預期路徑是否存在，並在使用商業邏輯前確認數值是否符合你的期望。 SQL Server 提供多項功能，幫助你在每個處理階段驗證 JSON 內容。

了解鬆散路徑模式與嚴格路徑模式

你可以在兩種模式中使用 JSON 路徑表達式來控制錯誤處理：

DECLARE @json NVARCHAR(MAX) = N'{"name": "Widget", "price": 29.99}';

-- Lax mode (default): Returns NULL for missing paths
SELECT JSON_VALUE(@json, 'lax $.description') AS LaxResult;

-- Strict mode: Raises an error for missing paths
SELECT JSON_VALUE(@json, 'strict $.description') AS StrictResult;

結果將是：

LaxResult
---------
NULL

-- Strict mode raises: Property cannot be found on the specified JSON path.

當預期會缺少屬性且應該返回lax時，使用NULL模式（預設模式）。當缺少屬性顯示資料有問題並應觸發錯誤時，請使用 strict 模式。

ISJSON 驗證字串是否包含有效的 JSON。下列範例示範如何使用 ISJSON：

SELECT 
    ISJSON('{"name": "test"}') AS ValidJson,      -- Returns 1
    ISJSON('not valid json') AS InvalidJson,       -- Returns 0
    ISJSON(NULL) AS NullJson;                      -- Returns NULL

結果將是：

ValidJson   InvalidJson   NullJson
---------   -----------   --------
1           0             NULL

為了妥善地處理無效資料，可以在 WHERE 子句中使用 ISJSON 來篩選出具有有效 JSON 的資料列，或在 CASE 運算式中使用。

JSON_PATH_EXISTS 檢查 JSON 文件中是否存在特定路徑，如下範例：

DECLARE @json NVARCHAR(MAX) = N'{"customer": {"name": "Contoso", "tier": "Gold"}}';

SELECT 
    JSON_PATH_EXISTS(@json, '$.customer.name') AS HasName,
    JSON_PATH_EXISTS(@json, '$.customer.email') AS HasEmail;

結果將是：

HasName   HasEmail
-------   --------
1         0

如果路徑存在，這個函式回傳 1，沒有則回傳 0。在嚴格模式下呼叫 JSON_VALUE 前使用它，或用來條件處理結構不同的 JSON。

用來 JSON_CONTAINS 檢查 JSON 文件是否包含特定值或物件，例如以下範例：

DECLARE @json NVARCHAR(MAX) = N'{"tags": ["sql", "database", "azure"]}';

SELECT 
    JSON_CONTAINS(@json, '"sql"', '$.tags') AS HasSqlTag,
    JSON_CONTAINS(@json, '"python"', '$.tags') AS HasPythonTag;

結果將是：

HasSqlTag   HasPythonTag
---------   ------------
1           0

用計算欄位優化 JSON 查詢

當你經常查詢特定的 JSON 屬性時，資料庫引擎必須解析每個查詢的每一列 JSON 文件。對於擁有數千甚至數百萬列的資料表，這種重複的解析會產生相當大的開銷。計算欄位讓你只提取一次 JSON 值，並以可查詢格式儲存，且支援索引。

為什麼 JSON 解析會影響效能

考慮一個有 100,000 筆產品紀錄的資料表，每列都包含帶有產品屬性的 JSON 文件。依類別進行查詢篩選必須：

從表格逐行讀取
要找到類別屬性，請解析 JSON 文件
提取並比較該數值

沒有優化，即使是簡單的篩選器，也必須對每一列進行完整的資料表掃描並進行 JSON 解析。

為 JSON 屬性建立計算欄位

計算出的欄位會自動擷取 JSON 屬性並將其作為一般欄位提供，如下範例：

-- Add a computed column that extracts a JSON property
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category');

-- The column is now available in queries
SELECT ProductID, ProductName, ProductCategory
FROM Products
WHERE ProductCategory = 'Electronics';

結果將是：

ProductID   ProductName           ProductCategory
---------   -------------------   ---------------
101         Wireless Mouse        Electronics
102         USB Keyboard          Electronics
103         HD Monitor            Electronics

預設情況下，計算出的欄位是虛擬的。資料庫會在查詢時計算值，但也能優化 JSON 擷取。為了提升效能，你可以像以下範例一樣持久化計算出的欄位：

-- Persisted computed column stores the extracted value physically
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED;

持久性欄位將擷取的值儲存在磁碟上，因此 JSON 解析只會在 INSERT 和 UPDATE 操作期間進行，而不在 SELECT 查詢時進行。

新增索引以加快過濾速度

真正的效能提升來自於對計算欄位的索引：

-- Create an index on the computed column
CREATE INDEX IX_Products_Category ON Products(ProductCategory);

-- Now this query uses an index seek instead of a table scan
SELECT ProductID, ProductName
FROM Products
WHERE ProductCategory = 'Electronics';

沒有索引時，查詢會掃描所有 100,000 筆資料。透過索引，查詢引擎執行索引尋道，並只檢索相符的列。此索引可將查詢時間從數秒縮短至毫秒。

索引多個 JSON 屬性

對於能過濾多個 JSON 屬性的查詢，請建立計算欄位和複合索引：

-- Extract multiple properties
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED,
    ProductBrand AS JSON_VALUE(ProductData, '$.brand') PERSISTED,
    ProductPrice AS CAST(JSON_VALUE(ProductData, '$.price') AS DECIMAL(10,2)) PERSISTED;

-- Create a composite index for common query patterns
CREATE INDEX IX_Products_Category_Brand ON Products(ProductCategory, ProductBrand);

-- Create an index for price range queries
CREATE INDEX IX_Products_Price ON Products(ProductPrice);

現在，透過類別和品牌篩選，或依價格排序，可以有效利用這些索引。

小提示

對於經常被存取的 JSON 屬性，計算出的欄位與索引相較於在查詢時解析 JSON 能提升查詢效能。監控你的查詢模式，並為用於 WHERE、 JOIN或 ORDER BY 子句的屬性建立計算欄位。

將關聯數據轉換為 JSON 格式，使用 FOR JSON

若要從查詢中完整輸出 JSON，請使用 FOR JSON PATH 或 FOR JSON AUTO：

SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    pc.Name AS CategoryName
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 1000
FOR JSON PATH, ROOT('products');

結果將是：

{"products":[{"ProductID":749,"Name":"Road-150 Red, 62","ListPrice":3578.27,"CategoryName":"Road Bikes"},{"ProductID":750,"Name":"Road-150 Red, 44","ListPrice":3578.27,"CategoryName":"Road Bikes"}]}

FOR JSON PATH 透過欄位別名控制 JSON 結構。在別名中使用點符號來建立巢狀物件：

SELECT 
    p.ProductID AS 'product.id',
    p.Name AS 'product.name',
    pc.Name AS 'product.category'
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID = 680
FOR JSON PATH;

結果將是：

[{"product":{"id":680,"name":"HL Road Frame - Black, 58","category":"Road Frames"}}]

欄位別名 'product.id' 會建立一個巢狀 product 物件，並帶有一個 id 屬性。此技術允許你將輸出調整成符合 API 預期格式，無需後製處理。

欲了解更多關於 SQL Server 中 JSON 函式的資訊，請參閱 SQL Server 中的 JSON 資料及 JSON 函式。

意見反應

此頁面對您有幫助嗎？

處理內建函式的 JSON 資料

用 JSON_VALUE 和 JSON_QUERY 提取數值

使用 OPENJSON 解析 JSON 陣列

構造包含JSON_OBJECT 和 JSON_ARRAY 的 JSON

彙整資料 JSON_ARRAYAGG

使用JSON_CONTAINS 驗證並檢查 JSON

了解鬆散路徑模式與嚴格路徑模式

用計算欄位優化 JSON 查詢

為什麼 JSON 解析會影響效能

為 JSON 屬性建立計算欄位

新增索引以加快過濾速度

索引多個 JSON 屬性

將關聯數據轉換為 JSON 格式，使用 FOR JSON

意見反應

使用 `OPENJSON` 解析 JSON 陣列

構造包含`JSON_OBJECT` 和 `JSON_ARRAY` 的 JSON

彙整資料 `JSON_ARRAYAGG`

使用`JSON_CONTAINS` 驗證並檢查 JSON