通过

JSON_MODIFY (Transact-SQL)

  • 项目

适用于：SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Microsoft Fabric 中的 SQL 分析终结点 Microsoft Fabric 中的仓库

更新 JSON 字符串中属性的值，并返回已更新的 JSON 字符串。

Transact-SQL 语法约定

语法

JSON_MODIFY ( expression , path , newValue )

参数

expression

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

如果 expression 不包含有效 JSON，则 JSON_MODIFY 返回错误。

path

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

path 具有以下语法：

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

  • append

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

  • lax

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

  • strict

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

  • <json path>

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

    在 SQL Server 2017 (14.x) 和 Azure SQL 数据库 中，可提供变量作为 path 的值。

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

newValue

path 指定的属性的新值。

新值必须是 varchar、nvarchar 或 text。

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

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

返回值

以正确格式化 JSON 文本的形式返回 expression 的更新值。

注解

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

下表对宽松模式和严格模式下 JSON_MODIFY 的行为进行了比较。 有关可选路径模式规范（宽松或严格）的详细信息，请参阅 JSON 路径表达式 (SQL Server)。

新值 路径存在 宽松模式 严格模式
NOT NULL 是 更新现有值。 更新现有值。
NOT NULL 否 尝试在指定路径上创建新的键值对。

这可能会造成失败。 例如，如果指定路径$.user.setting.theme，则如果不存在或$.user.settings对象不存在，或者设置是数组或标量值，则不插入键theme$.user。 JSON_MODIFY		 错误 - INVALID_PROPERTY
NULL 是 删除现有属性。 将现有值设置为 NULL。
NULL 否 无操作。 返回第一个参数作为结果。 错误 - INVALID_PROPERTY

在宽松模式下，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_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 文本）。 因此，函数的 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;

相关内容