Поделиться через

JSON_MODIFY (Transact-SQL)

  • Статья

Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure конечную точку аналитики SQL Azure Synapse Analytics в хранилище Microsoft Fabric в Microsoft Fabric

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

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

Синтаксис

JSON_MODIFY ( expression , path , newValue )

Аргументы

выражение

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

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

path

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

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

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

  • append

    Необязательный модификатор, который указывает, что новое значение следует добавить в массив, на который ссылается <json path>.

  • lax

    Указывает, что свойство, <json path> на которое ссылается ссылка, не обязательно существует. Если свойство отсутствует, JSON_MODIFY пытается вставить новое значение по указанному пути. Вставка может завершиться ошибкой, если свойство невозможно вставить в путь. Если строгий или нестрогий режим не указан, по умолчанию используется нестрогий режим (lax).

  • строгий_режим

    Указывает, что свойство, на которое ссылается <json path>, должно быть в выражении JSON. Если свойство отсутствует, JSON_MODIFY возвращает ошибку.

  • <json path>

    Указывает путь до обновляемого свойства. Дополнительные сведения см. в статье Выражения пути JSON (SQL Server).

    В SQL Server 2017 (14.x) и в База данных SQL Azure можно указать переменную в качестве значения пути.

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

newValue

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

Новое значение должно быть varchar, nvarchar или text.

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

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

Возвращаемое значение

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

Замечания

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

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

Новое значение Путь существует Нестрогий режим Строгий режим
NOT NULL Да Обновить существующее значение. Обновить существующее значение.
NOT NULL No Попробуйте создать новую пару "ключ-значение" по указанному пути.

Это может завершиться ошибкой. Например, если указать путь $.user.setting.theme, JSON_MODIFY не вставляет ключ theme , если $.user $.user.settings объекты не существуют, или если параметры являются массивом или скалярным значением.		 Ошибка — INVALID_PROPERTY
NULL Да Удалить существующее свойство. Установить существующее значение в NULL.
NULL No Никаких действий не выполняется. В качестве результата возвращается первый аргумент. Ошибка — INVALID_PROPERTY

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

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

Примеры

А. Базовые операции

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

В. Переименование ключа

В следующем примере показано, как переименовать свойство в тексте 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
}

Е. Изменение объекта 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;

Связанный контент