通过

JSON_MODIFY（Transact-SQL）

适用于：sql Server 2016 （13.x） 及更高版本 Azure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsSQL 分析终结点Microsoft FabricWarehouse 中的Microsoft Fabric SQL 数据库在 Microsoft Fabric 中的 fabric SQL 数据库

语法 JSON_MODIFY 更新 JSON 字符串中属性的值，并返回更新后的 JSON 字符串。

Transact-SQL 语法约定

Syntax

JSON_MODIFY ( expression , path , newValue )

Arguments

expression

表达式。 通常是包含 JSON 文本的变量或列的名称。

JSON_MODIFY 如果 表达式 不包含有效的 JSON，则返回错误。

path

指定要更新的属性的 JSON 路径表达式。

路径 具有以下语法：

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

  • append

    指定应将新值追加到通过 引用的数组的可选修饰符<json path>。

  • lax

    指定引用的属性 <json path> 不必存在。 如果该属性不存在， JSON_MODIFY 则尝试在指定路径上插入新值。 如果无法在路径上插入属性，则插入可能会失败。 如果未指定 宽松 或 严格， 则宽松 是默认模式。

  • strict

    指定通过 引用的属性必须处于 JSON 表达式中<json path>。 如果该属性不存在， JSON_MODIFY 则返回错误。

  • <json path>

    为要更新的属性指定路径。 有关详细信息，请参阅 JSON 路径表达式。

    在 SQL Server 2017（14.x）和 Azure SQL 数据库中，可以将变量作为 路径的值提供。

    JSON_MODIFY 如果 路径 格式无效，则返回错误。

newValue

路径指定的属性的新值。

新值必须是 varchar、nvarchar、char、tinyint、smallint、int、bigint、bit、decimal/ 或 real/。 不支持 文本 数据类型。

在宽松模式下，如果新值为 JSON_MODIFY，则 NULL 会删除指定键。

JSON_MODIFY 如果值的类型为 varchar 或 nvarchar，则转义新值中的所有特殊字符。 如果文本值的格式 FOR JSON正确，则文本值不会转义， JSON_QUERY或者 JSON_MODIFY。

返回值

将 表达式 的更新值作为格式正确的 JSON 文本返回。

Remarks

该 JSON_MODIFY 函数允许更新现有属性的值、插入新的键：值对，或者根据模式和提供的值的组合删除键。

下表对宽松模式和严格模式下 JSON_MODIFY 的行为进行了比较。 有关可选路径模式规范（宽松或严格）的详细信息，请参阅 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

在宽松模式下，JSON_MODIFY 会尝试创建新的键:值对，但在某些情况下可能会失败。

无论 JSON 文档存储在 varchar、 nvarchar 还是本机 json 数据类型中，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_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 会将它视为文本，并用双引号将它括起。

D. 递增值

下面的示例演示如何使用 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
}

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