共用方式為

JSON_MODIFY（Transact-SQL）

適用於： SQL Server 2016 （13.x） 及以後版本 Azure SQL Database AzureSQL Managed InstanceAzure Synapse AnalyticsSQL Analytics endpoint in Microsoft FabricWarehouse in Microsoft FabricSQL database in Microsoft Fabric

語法會 JSON_MODIFY 更新 JSON 字串中的屬性值，並傳回更新的 JSON 字串。

Transact-SQL 語法慣例

Syntax

JSON_MODIFY ( expression , path , newValue )

Arguments

expression

一種表達方式。 通常為變數的名稱或包含 JSON 文字的資料行。

JSON_MODIFY 如果 expression 不包含有效的 JSON，則傳回錯誤。

path

指定要更新之屬性的 JSON 路徑運算式。

path 具有下列語法：

[append] [ lax | strict ] $.<json path>

  • append

    選擇性修飾詞，指定應該將新值附加至 所 <json path>參考的陣列。

  • lax

    指定所 <json path> 參考的屬性不需要存在。 如果屬性不存在， JSON_MODIFY 嘗試在指定的路徑上插入新的值。 若屬性無法在路徑上插入，則插入會失敗。 如果您未指定 lax 或 strict，lax 是預設模式。

  • strict

    指定所 <json path> 參考的屬性必須位於 JSON 運算式中。 如果屬性不存在， JSON_MODIFY 則傳回錯誤。

  • <json path>

    指定要更新之屬性的路徑。 如需詳細資訊，請參閱 JSON 路徑表達式。

    在 SQL Server 2017 （14.x） 和 Azure SQL Database 中，您可以提供變數作為 路徑的值。

    JSON_MODIFY 如果 路徑 的格式無效，則傳回錯誤。

newValue

path 所指定之屬性的新值。

新的值必須是 varchar、nvarchar、char、tinyint、smallint、int、bigint、bit、decimal/ 或 real/。 不支援 文字 數據類型。

在 lax 模式中，若新值為 JSON_MODIFY，則 NULL 會刪除指定的索引鍵。

JSON_MODIFY 如果值的類型為 varchar 或 nvarchar，則會逸出新值中的所有特殊字元。 如果文字值的格式正確，則 FOR JSON不會逸出 、 JSON_QUERY或 JSON_MODIFY所產生的 JSON。

返回值

以正確格式的 JSON 文字傳回 表示式 的更新值。

Remarks

函 JSON_MODIFY 式可讓您更新現有屬性的值、插入新的key：value組，或根據模式組合和提供的值來刪除索引鍵。

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

新值 路徑存在 鬆弛模式 嚴格模式
NOT NULL Yes 更新現有值。 更新現有值。
NOT NULL No 嘗試在指定的路徑上建立新的機碼/值組。

這可能會失敗。 例如，如果您指定路徑 $.user.setting.theme，JSON_MODIFY如果 theme 或 $.user 物件不存在，或設定是陣列或純量值，則不會插入索引鍵$.user.settings。		 錯誤 - INVALID_PROPERTY
NULL Yes 刪除現有屬性。 將現有值設定為 null。
NULL No 沒有行動。 第一個引數會作為結果傳回。 錯誤 - INVALID_PROPERTY

在 lax 模式中，JSON_MODIFY 會嘗試建立新的索引鍵/值組，但在某些案例下可能會失敗。

JSON 函式的運作方式與 JSON 檔案儲存在 varchar、 nvarchar 或原生 json 數據類型相同。

Examples

A. 基本作業

下列範例示範可使用 JSON 文字進行的基本作業。

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Update name
SET @info = JSON_MODIFY(@info, '$.name', 'Mike');
PRINT @info;

-- Insert surname
SET @info = JSON_MODIFY(@info, '$.surname', 'Smith');
PRINT @info;

-- Set name NULL
SET @info = JSON_MODIFY(@info, 'strict $.name', NULL);
PRINT @info;

-- Delete name
SET @info = JSON_MODIFY(@info, '$.name', NULL);
PRINT @info;

-- Add skill
SET @info = JSON_MODIFY(@info, 'append $.skills', 'Azure');
PRINT @info;

結果集如下所示。

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

B. 多次更新

使用 JSON_MODIFY時，您只能更新一個屬性。 如果您必須執行多個更新，您可以使用多個 JSON_MODIFY 呼叫。

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Multiple updates
SET @info = JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(@info, '$.name', 'Mike'), '$.surname', 'Smith'), 'append $.skills', 'Azure');
PRINT @info;

結果集如下所示。

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

C. 重新命名金鑰

下列範例示範如何使用 函式重新命名 JSON 文字 JSON_MODIFY 中的屬性。 首先，您可以使用現有屬性的值，並將其插入為新的索引鍵/值組。 然後，您可以將 old 屬性的值設定為 NULL，以刪除舊的索引鍵。

DECLARE @product NVARCHAR(100) = '{"price":49.99}';
PRINT @product;

-- Rename property
SET @product = JSON_MODIFY(JSON_MODIFY(@product, '$.Price', CAST(JSON_VALUE(@product, '$.price') AS NUMERIC(4, 2))), '$.price', NULL);
PRINT @product;

結果集如下所示。

{
    "price": 49.99
} {
    "Price": 49.99
}

若您沒有將新的值轉換成數值類型，JSON_MODIFY 便會將其當作文字處理，並用雙引號括住。

D. 遞增值

下列範例示範如何使用 JSON_MODIFY 函數遞增 JSON 文字中的屬性。 首先，您可以使用現有屬性的值，並將其插入為新的索引鍵/值組。 然後，您可以將 old 屬性的值設定為 NULL，以刪除舊的索引鍵。

DECLARE @stats NVARCHAR(100) = '{"click_count": 173}';
PRINT @stats;

-- Increment value
SET @stats = JSON_MODIFY(@stats, '$.click_count', CAST(JSON_VALUE(@stats, '$.click_count') AS INT) + 1);
PRINT @stats;

結果集如下所示。

{
    "click_count": 173
} {
    "click_count": 174
}

E. 修改 JSON 物件

JSON_MODIFY 即使 newValue 自變數包含格式正確的 JSON 文字，仍會將 newValue 自變數視為純文本。 因此，函式的 JSON 輸出會由雙引號括住，並且所有的特殊字元都會遭到逸出，如下列範例中所示。

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Update skills array
SET @info = JSON_MODIFY(@info, '$.skills', '["C#","T-SQL","Azure"]');
PRINT @info;

結果集如下所示。

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": "[\"C#\",\"T-SQL\",\"Azure\"]"
}

若要避免自動逸出，請使用 函式提供 JSON_QUERY。 JSON_MODIFY 知道 JSON_QUERY 所傳回值是格式正確的 JSON，因此不會逸出該值。

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Update skills array
SET @info = JSON_MODIFY(@info, '$.skills', JSON_QUERY('["C#","T-SQL","Azure"]'));
PRINT @info;

結果集如下所示。

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": ["C#", "T-SQL", "Azure"]
}

F. 更新 JSON 數據行

下列範例會更新包含 JSON 之資料表資料行中屬性的值。

UPDATE Employee
SET jsonCol = JSON_MODIFY(jsonCol, '$.info.address.town', 'London')
WHERE EmployeeID = 17;

相關內容

  • Last updated on