共用方式為

JSON_MODIFY (Transact-SQL)

  • 發行項

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

更新 JSON 字串中的屬性值，並傳回更新後的 JSON 字串。

Transact-SQL 語法慣例

語法

JSON_MODIFY ( expression , path , newValue )

引數

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)。

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

    若 path 的格式無效，JSON_MODIFY 便會傳回錯誤。

newValue

path 指定之屬性的新值。

新的值必須是 varchar、nvarchar 或 text。

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

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

傳回值

將 expression 的更新值以格式正確的 JSON 文字傳回。

備註

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

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

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

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

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

無論 JSON 文件是以 varchar、nvarchar 或原生 json 資料類型儲存，JSON 函數的運作方式皆相同。

範例

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 文字。 因此，函式的 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 函數來提供 newValue。 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;

相關內容