共用方式為

JSON 路徑運算式 (SQL Server)

適用於： SQL Server 2016 (13.x) 以上版本 Azure SQL 資料庫Azure SQL 受控執行個體Azure Synapse Analytics (僅無伺服器 SQL 集區)

請使用 JSON 路徑運算式來參考 JSON 物件的屬性。

當您呼叫下列函數時，必須提供路徑運算式。

  • 呼叫 OPENJSON 以建立 JSON 資料的關聯檢視時。
  • 呼叫 JSON_VALUE 以從 JSON 文字擷取值時。
  • 呼叫 JSON_QUERY 以擷取 JSON 物件或陣列時。
  • 呼叫 JSON_MODIFY 以更新 JSON 字串中的屬性值時。

路徑運算式的組成部分

路徑運算式有兩個元件。

  1. 選擇性 路徑模式，值為 lax 或 strict。

  2. 路徑 本身。

路徑模式

在路徑表達式的開頭，選擇性地藉由指定 關鍵詞 lax 或 strict來宣告路徑模式。 預設值為 lax。

  • 在 lax 模式中，如果路徑表示式包含錯誤，函式會傳回空白值。 例如，如果您要求 值 $.name，且 JSON 文字不包含 name 索引鍵，則函式會傳回 null，但不會引發錯誤。

  • 在 strict 模式中，如果路徑表示式包含錯誤，函式就會引發錯誤。

下列查詢會在路徑運算式中明確指定 lax模式。

DECLARE @json AS NVARCHAR (MAX);

SET @json = N'{ ... }';

SELECT *
FROM OPENJSON (@json, N'lax $.info');

路徑

選擇性地宣告路徑模式之後，請指定路徑本身。

  • 貨幣符號 ($) 表示內容項目。

  • 屬性路徑是一組路徑步驟。 路徑步驟可包含下列元素和運算子。

    • 索引鍵名稱。 例如，$.name 與 $."first name"。 如果索引鍵名稱以貨幣符號開頭或包含特殊字元 (例如空格或點運算子 (.))，請用引號括住。

    • 陣列元素。 例如： $.product[3] 。 以零為基底的陣列。

    • 點運算子 (.) 表示物件的成員。 例如，在 $.people[1].surname 中，surname 是 people 的子系。

    • 如果輸入是 JSON 類型值，也支援數位通配符和範圍搜尋。

陣列通配符和範圍支援

注意

陣列通配符和範圍支援目前為預覽狀態，僅適用於 SQL Server 2025 （17.x） 預覽版。

SQL Server 2025 (17.x) 預覽版擴展了 ANSI SQL/JSON 路徑運算式，以支援陣列通配符。 Array 通配符可讓您指定所有元素、元素範圍、元素清單或使用特殊符號 'last' 來表示 JSON 陣列中的最後一個值。 SQL/JSON 陣列使用以零起始的索引。 搭配通配符的 SQL/JSON 路徑可用於 JSON_QUERY、 JSON_PATH_EXISTS和 JSON_CONTAINS。

雖然 JSON_VALUE 函式支援 SQL/JSON 路徑表示式，但函式的 JSON_VALUE 傳回值是 SQL 純量，因此函式一律會針對指向 JSON 物件或數位的任何 SQL/JSON 路徑傳回 NULL 。 只有在輸入是 json 類型時，才支援陣列通配符。

下列語法示範如何使用通配符、範圍和特殊令牌 last ：

path[elements ]

elements ::= {
*
| number
| number to number
| last
| {number...[, number] }
}

特殊令牌 last 可用於取代數位值。 如果指定範圍，則必須以遞增順序指定範圍。

一些有效的 SQL/JSON 路徑表示式範例：

路徑 說明
$[*] 所有元素
$[0] 第一個元素
$[0 to 2] 前三個元素
$[last] 最後元素
$[last, 0] 無效
$[last, 2, 0, last] 無效
$.creditcards[0].type 傳回 creditcards 陣列中第一個元素的類型屬性值。
$.credit_cards[*].type 傳回 creditcards 陣列中所有元素的型別屬性值
$.credit_cards[0, 2].type 傳回creditcards數組中第一個和第三個元素的類型屬性值
$.credit_cards[1 to 3].type 傳回 creditcards 陣列中第二到第四個元素的類型屬性值
$.credit_cards[last].type 傳回陣列 creditcards 最後一個元素之類型屬性值
$.credit_cards[last, 0].type 傳回 creditcards 陣列中第一個和最後一個元素的類型屬性值

範例

本節中的範例參考下列 JSON 文字。

{
    "people": [{
        "name": "John",
        "surname": "Doe"
    }, {
        "name": "Jane",
        "surname": null,
        "active": true
    }]
}

下表顯示路徑運算式的一些範例。

路徑運算式 值
$.people[0].name John
$.people[1] { "name": "Jane", "surname": null, "active": true }
$.people[1].surname NULL
$ { "people": [ { "name": "John", "surname": "Doe" },{ "name": "Jane", "surname": null, "active": true } ] }
$.people[last].name ["Jane"]
$.people[0 to 1].name ["John","Jane"]
$.people[0, 1].name ["John","Jane"]

內建函數處理重複路徑的方法

如果 JSON 文字包含重複的屬性 -例如，相同層級上具有相同名稱的兩個索引鍵 - JSON_VALUE 和 函 JSON_QUERY 式只會傳回符合路徑的第一個值。 若要剖析包含重複索引鍵並傳回所有值的 JSON 物件，請使用 OPENJSON，如下列範例所示。

DECLARE @json AS NVARCHAR (MAX);

SET @json = N'{"person":{"info":{"name":"John", "name":"Jack"}}}';

SELECT value
FROM OPENJSON (@json, '$.person.info');

深入了解 SQL Server 和 Azure SQL Database 中的 JSON

如需 SQL Server 和 Azure SQL Database 中內建 JSON 支援的視覺效果簡介，請參閱下列影片：

相關內容