JSON_MODIFY (Transact-SQL)

  • 發行項

適用於：SQL Server 2016 (13.x) 及更新版本 Azure SQL DatabaseAzure 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 路徑> 所參考的陣列。

  • lax
    指定 <JSON 路徑> 所參考的屬性不一定要存在。 若屬性不存在，JSON_MODIFY 便會嘗試在指定的路徑插入新值。 若屬性無法在路徑上插入，插入可能會失敗。 若您未指定 lax 或 strict，則預設模式為 lax。

  • strict
    指定 <JSON 路徑> 所參考的屬性必須存在於 JSON 運算式中。 若屬性不存在，JSON_MODIFY 會傳回錯誤。

  • <JSON 路徑>
    指定要更新之屬性的路徑。 如需詳細資訊，請參閱 JSON 路徑運算式 (SQL Server)。

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

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

newValue
path 指定之屬性的新值。
新的值必須是 [n]varchar 或 text。

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

JSON_MODIFY 會逸出所有類型為 NVARCHAR 或 VARCHAR 新值中的特殊字元。 若文字值為 FOR JSON、JSON_QUERY 或 JSON_MODIFY 所產生之格式正確的 JSON，則文字值便不會逸出。

傳回值

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

備註

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

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

新值 路徑存在 Lax 模式 Strict 模式
非 NULL 是 更新現有值。 更新現有值。
非 NULL 否 嘗試在指定的路徑上建立新的索引鍵/值組。

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

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

範例

範例 - 基本作業

下列範例示範可使用 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"
}

範例 - 多個更新

使用 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"
}

範例 - 重新命名索引鍵

下列範例示範如何使用 JSON_MODIFY 函式重新命名 JSON 文字中的屬性。 首先，您可以使用現有屬性的值，並將其插入為新的索引鍵/值組。 然後您便可以透過將舊屬性的值設為 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 便會將其當作文字處理，並用雙引號括住。

範例 - 遞增值

下列範例示範如何使用 JSON_MODIFY 函式遞增 JSON 文字中的屬性。 首先，您可以使用現有屬性的值，並將其插入為新的索引鍵/值組。 然後您便可以透過將舊屬性的值設為 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
}

範例 - 修改 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"]
}

範例 - 更新 JSON 資料行

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

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

另請參閱