JSON_MODIFY (Transact-SQL)

Применимо к: SQL Server 2016 (13.x) и более поздним версиям,Azure SQL Database,Azure SQL Managed Instance,Azure Synapse Analytics,конечной точке аналитики SQL в Microsoft Fabric,хранилищу в Microsoft Fabric,SQL базе данных в предварительной версии Microsoft Fabric

Синтаксис JSON_MODIFY обновляет значение свойства в строке JSON и возвращает обновленную строку JSON.

Соглашения о синтаксисе Transact-SQL

Syntax

JSON_MODIFY ( expression , path , newValue )

Arguments

expression

An expression. Обычно имя переменной или столбца, содержащего текст JSON.

JSON_MODIFY возвращает ошибку, если выражение не содержит допустимого JSON.

path

Выражение пути JSON, в котором указано обновляемое свойство.

путь имеет следующий синтаксис:

[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) и в Базе данных SQL Azure можно указать переменную в качестве значения пути.

  JSON_MODIFY Возвращает ошибку, если формат пути недействителен.

newValue

Новое значение для свойства, указанного путем.

Новое значение должно быть varchar, nvarchar, char, tinyint, smallint, int, bigint, bit, decimal/numeric или real/float. Тип текстовых данных не поддерживается.

В режиме lax удаляет указанный ключ, JSON_MODIFY если новое значение равно NULL.

JSON_MODIFY экранирует все специальные символы в новом значении, если тип значения — varchar или nvarchar. Текстовое значение не экранируется, если оно правильно отформатировано JSON, созданное FOR JSON, JSON_QUERYили JSON_MODIFY.

Return value

Возвращает обновленное значение выражения должным образом отформатированный текст JSON.

Remarks

Функция JSON_MODIFY позволяет обновить значение существующего свойства, вставить новую пару key:value или удалить ключ на основе сочетания режимов и предоставленных значений.

В следующей таблице сравнивается поведение JSON_MODIFY в нестрогом режиме и в строгом режиме. Дополнительные сведения о спецификации режима необязательного пути (lax или strict) см. в выражениях пути JSON.

В режиме JSON_MODIFY lax пытается создать новую пару key:value, но в некоторых случаях это может завершиться ошибкой.

Функции JSON работают одинаково, хранится ли документ JSON в varchar, nvarchar или в собственном типе данных JSON .

Examples

A. Basic operations

В следующем примере показаны основные операции, которые можно выполнить с текстом 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. Multiple updates

С помощью 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 помощью функции. Сначала можно взять значение существующего свойства и вставить его как новую пару ключ-значение. Затем можно удалить старый ключ, задав значение старого свойства 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 с JSON_MODIFY помощью функции. Сначала можно взять значение существующего свойства и вставить его как новую пару ключ-значение. Затем можно удалить старый ключ, задав значение старого свойства 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 обрабатывает новый аргументValue как обычный текст, даже если он содержит правильно отформатированный текст 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 экранирования, предоставьте новое значение с помощью функции. 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;

Related content

• Выражения пути JSON
• Данные JSON в SQL Server