共用方式為

JSON_QUERY （Transact-SQL）

適用於：SQL Server 2016 (13.x) 和更新版本 Azure SQL 資料庫Azure SQL 受控執行個體Azure Synapse AnalyticsMicrosoft Fabric 中的 SQL 分析端點Microsoft Fabric 中的倉儲

從 JSON 字串擷取物件或陣列。

若要從 JSON 字串擷取純量值，而不是物件或陣列，請參閱 JSON_VALUE。 如需 JSON_VALUE 及 JSON_QUERY 之間的差異資訊，請參閱比較 JSON_VALUE 與 JSON_QUERY。

Transact-SQL 語法慣例

語法

JSON_QUERY ( expression [ , path ] [WITH ARRAY WRAPPER])

引數

表示式

運算式。 通常為變數的名稱或包含 JSON 文字的資料行。

如果在JSON_QUERY表達式中尋找路徑所識別的值之前，在表達式中找不到無效的 JSON，則函式會傳回錯誤。 如果 JSON_QUERY 找不到 路徑所識別的值，它會掃描整個文字，並在找到 表達式中任何位置無效的 JSON 時傳回錯誤。

路徑

指定要擷取之物件或陣列的 JSON 路徑。

在 SQL Server 2017 (14.x) 和 Azure SQL Database 中，您可以提供變數作為 path 的值。

JSON 路徑可為剖析指定 lax 或 strict 模式。 若您未指定剖析模式，預設會使用 lax 模式。 如需詳細資訊，請參閱 JSON 路徑運算式 (SQL Server)。

路徑的預設值為 $。 如此一來，若您未提供 path 的值，JSON_QUERY 會傳回輸入的 expression。

如果 path 的格式無效，則 JSON_QUERY 會傳回錯誤。

WITH ARRAY WRAPPER

備註

WITH ARRAY WRAPPER 目前處於預覽狀態，且僅適用於 SQL Server 2025 （17.x） 預覽版。

ANSI SQL JSON_QUERY 函式目前用來傳回指定路徑中的 JSON 物件或陣列。 支援 SQL Server 2025 （17.x） Preview 中引進的 SQL/JSON 路徑運算式中的 陣列通配符 ， JSON_QUERY 可用來傳回 JSON 陣列中每個元素都是 JSON 物件之元素的指定屬性。 由於通配符搜尋可以傳回多個值，因此請在 JSON 查詢運算式中指定 WITH ARRAY WRAPPER 子句，以及具有通配符或範圍或清單的 SQL/JSON 路徑表達式，以傳回值做為 JSON 陣列。 WITH ARRAY WRAPPER 只有在輸入是 json 類型時，才支援 子句。

請考慮下列 JSON 檔：

declare @j JSON = '{
    "id": 2,
    "first_name": "Mamie",
    "last_name": "Baudassi",
    "email": "mbaudassi1@abc.net.au",
    "gender": "Female",
    "ip_address": "148.199.129.123",
    "credit_cards": [
        {
            "type": "jcb",
            "card#": "3545138777072343",
            "currency": "Koruna"
        },
        {
            "type": "diners-club-carte-blanche",
            "card#": "30282304348533",
            "currency": "Dong"
        },
        {
            "type": "jcb",
            "card#": "3585303288595361",
            "currency": "Yuan Renminbi"
        },
        {
            "type": "maestro",
            "card#": "675984450768756054",
            "currency": "Rupiah"
        },
        {
            "type": "instapayment",
            "card#": "6397068371771473",
            "currency": "Euro"
        }
    ]
}';

路徑 $.credit_cards 會指向 JSON 陣列，其中每個元素都是有效的 JSON 物件。 現在，函 JSON_QUERY 式可與陣列通配符支援搭配使用，以傳回屬性的所有或特定值， type 例如：

SELECT JSON_QUERY(@j, '$.creditcards[*].type' WITH ARRAY WRAPPER);

下表顯示使用 通配符和傳回值 JSON_QUERY WITH ARRAY WRAPPER的各種 SQL/JSON 路徑表示式範例。

路徑 傳回值
$.creditcards[0].type ["jcb"]
$.credit_cards[*].type ["jcb","diners-club-carte-blanche","jcb","maestro","instapayment"]
$.credit_cards[0, 2].type ["jcb","jcb"]
$.credit_cards[1 to 3].type ["diners-club-carte-blanche","jcb","maestro"]
$.credit_cards[last].type ["instapayment"]
$.credit_cards[last, 0].type ["instapayment","jcb"]
$.credit_cards[last, last].type ["instapayment","instapayment"]
$.credit_cards[ 0, 2, 4].type ["jcb","jcb","instapayment"]

傳回值

傳回類型為 nvarchar(max) 的 JSON 片段。 傳回值的定序與輸入運算式的定序相同。

如果值不是物件或陣列：

  • 在 lax 模式中，JSON_QUERY 會傳回 Null。

  • 在 strict 模式中，JSON_QUERY 會傳回錯誤。

備註

lax 模式和 strict 模式

請參考下列 JSON 文字：

{
   "info": {
      "type": 1,
      "address": {
         "town": "Cheltenham",
         "county": "Gloucestershire",
         "country": "England"
      },
      "tags": ["Sport", "Water polo"]
   },
   "type": "Basic"
}

下列表格會比較 lax 模式與 strict 模式中 JSON_QUERY 的行為。 如需選擇性路徑模式規格 (lax 或 strict) 的詳細資訊，請參閱 JSON 路徑運算式 (SQL Server)。

路徑 lax 模式中的傳回值 strict 模式中的傳回值 其他資訊
$ 傳回完整的 JSON 文字。 傳回完整的 JSON 文字。
$.info.type NULL 錯誤 並非物件或陣列。

請改用 JSON_VALUE。
$.info.address.town NULL 錯誤 並非物件或陣列。

請改用 JSON_VALUE。
$.info."address" N'{ "town":"Cheltenham", "county":"Gloucestershire", "country":"England" }' N'{ "town":"Cheltenham", "county":"Gloucestershire", "country":"England" }'
$.info.tags N'[ "Sport", "Water polo"]' N'[ "Sport", "Water polo"]'
$.info.type[0] NULL 錯誤 非陣列。
$.info.none NULL 錯誤 屬性不存在。

搭配 FOR JSON 使用 JSON_QUERY

JSON_QUERY 會傳回有效的 JSON 片段。 因此，FOR JSON 不會逸出 JSON_QUERY 傳回值中的特殊字元。

若您使用 FOR JSON 傳回結果，並且在其中包含已經是 JSON 格式的資料 (在資料行中或運算式的結果)，請使用不具有 JSON_QUERY 參數的 來包裝 JSON 資料。

範例

A。 傳回 JSON 片段

下列範例示範如何在查詢結果中傳回來自 CustomFields 資料行的 JSON 片段。

SELECT PersonID,
       FullName,
       JSON_QUERY(CustomFields, '$.OtherLanguages') AS Languages
FROM Application.People;

B. 在 FOR JSON 輸出中包含 JSON 片段

下列範例示範如何在 FOR JSON 子句的輸出中包含 JSON 片段。

SELECT StockItemID,
       StockItemName,
       JSON_QUERY(Tags) AS Tags,
       JSON_QUERY(CONCAT('["', ValidFrom, '","', ValidTo, '"]')) AS ValidityPeriod
FROM Warehouse.StockItems
FOR JSON PATH;

C. 搭配 JSON_QUERY 函式使用WITH ARRAY WRAPPER

下列範例示範 搭配 函式使用 WITH ARRAY WRAPPERJSON_QUERY ，從 JSON 陣列傳回多個元素：

DECLARE @j JSON = ' {"id":2,"first_name":"Mamie","last_name":"Baudassi","email":"mbaudassi1@abc.net.au","gender":"Female","ip_address":"148.199.129.123","credit_cards":[{"type":"jcb","card#":"3545138777072343","currency":"Koruna"},{"type":"diners-club-carte-blanche","card#":"30282304348533","currency":"Dong"},{"type":"jcb","card#":"3585303288595361","currency":"Yuan Renminbi"},{"type":"maestro","card#":"675984450768756054","currency":"Rupiah"},{"type":"instapayment","card#":"6397068371771473","currency":"Euro"}]}
';
SELECT JSON_QUERY(@j, '$.credit_cards[*].type' WITH ARRAY WRAPPER ) as credit_card_types;

結果如下。

credit_card_types
--------
["jcb","diners-club-carte-blanche","jcb","maestro","instapayment"]

相關內容