JSON MODIFY: примеры (SQL)
JSON_MODIFY(json_expression, path, new_value): nvarcharОписание функции JSON_MODIFY
Функция JSON_MODIFY изменяет значение свойства в строке JSON и возвращает обновленную строку JSON. Она используется при необходимости обновить, добавить или удалить пары ключ-значение в JSON-объектах или изменить элементы в JSON-массивах прямо в запросе T-SQL, без необходимости полного разбора и пересборки JSON.
Синтаксис функции: JSON_MODIFY (expression, path, newValue)
- expression (Входной параметр): Исходная строка JSON. Может быть переменной, столбцом или выражением, возвращающим допустимый текст JSON. Если значение не является допустимым JSON, функция вызывает ошибку.
- path (Входной параметр): Путь JSON к изменяемому свойству. Синтаксис пути: '$.ключ' для свойств верхнего уровня или '$.ключ[индекс]' для массивов. Путь может включать несколько уровней вложенности, например, '$.объект.свойство'.
- newValue (Входной параметр): Новое значение для свойства, указанного в path. Может быть значением любого типа данных SQL (nvarchar, int, datetime и т.д.), которое будет преобразовано в JSON. Если указать NULL, свойство будет удалено. Чтобы задать значение JSON null, используйте ключевое слово 'null' в строке. Можно использовать встроенные функции JSON, такие как JSON_QUERY(), для вставки объектов или массивов без экранирования.
Возвращаемое значение: Функция возвращает обновленную строку JSON в формате NVARCHAR(MAX). Если входная строка не является допустимым JSON, возвращается ошибка. Если путь содержит несуществующие элементы (кроме конечного свойства), ошибка не возникает, но изменения не применяются.
Простые примеры использования
Изменение существующего свойства объекта:
DECLARE @json NVARCHAR(MAX) = N'{"name": "Иван", "age": 30}'
SELECT JSON_MODIFY(@json, '$.age', 31) AS UpdatedJson;{"name": "Иван", "age": 31}Добавление нового свойства в объект:
DECLARE @json NVARCHAR(MAX) = N'{"name": "Иван"}'
SELECT JSON_MODIFY(@json, '$.city', N'Москва') AS UpdatedJson;{"name": "Иван", "city": "Москва"}Удаление свойства (использование NULL в качестве нового значения):
DECLARE @json NVARCHAR(MAX) = N'{"name": "Иван", "age": 30}'
SELECT JSON_MODIFY(@json, '$.age', NULL) AS UpdatedJson;{"name": "Иван"}Изменение элемента в массиве по индексу:
DECLARE @json NVARCHAR(MAX) = N'{"items": ["яблоко", "банан", "вишня"]}'
SELECT JSON_MODIFY(@json, '$.items[1]', 'апельсин') AS UpdatedJson;{"items": ["яблоко", "апельсин", "вишня"]}Установка значения JSON null (строка 'null'):
DECLARE @json NVARCHAR(MAX) = N'{"name": "Иван"}'
SELECT JSON_MODIFY(@json, '$.middleName', 'null') AS UpdatedJson;{"name": "Иван", "middleName": null}Похожие функции в MS SQL Server
JSON_VALUE: Извлекает скалярное значение из строки JSON по указанному пути. Используется, когда нужно получить одиночное значение (строку, число, логическое значение) из JSON для использования в условиях WHERE или SELECT.
JSON_QUERY: Извлекает объект или массив (фрагмент JSON) из строки JSON. Полезно, когда нужно получить вложенный JSON без разбора. Часто используется вместе с JSON_MODIFY для вставки сложных структур.
OPENJSON: Табличная функция, которая преобразует строку JSON в набор строк и столбцов. Это основной инструмент для разбора JSON, когда требуется работать с данными в реляционном формате. Предпочтительнее использовать OPENJSON для сложных операций, где нужно обработать несколько элементов массива или преобразовать JSON в таблицу для соединений.
ISJSON: Проверяет, является ли строка допустимым JSON. Рекомендуется использовать перед вызовом функций JSON, чтобы избежать ошибок.
Выбор функции зависит от задачи: JSON_MODIFY предназначена для модификации, JSON_VALUE для извлечения скаляров, JSON_QUERY для извлечения фрагментов, а OPENJSON для полного преобразования в табличный вид.
Типичные ошибки
Некорректный формат входного JSON приводит к ошибке.
DECLARE @invalidJson NVARCHAR(MAX) = N'{name: "Иван"}' -- Нет кавычек у ключа
SELECT JSON_MODIFY(@invalidJson, '$.age', 30);Ошибка: Недопустимые символы JSON обнаружены в контексте ''.
Использование пути к несуществующему объекту для вставки сложного значения без явного создания объекта. Функция не создает промежуточные объекты автоматически.
DECLARE @json NVARCHAR(MAX) = N'{}'
-- Попытка добавить свойство во вложенный объект, которого нет
SELECT JSON_MODIFY(@json, '$.address.city', N'Москва');{}Для решения этой проблемы можно использовать вложенные вызовы JSON_MODIFY или предварительно создать структуру с помощью JSON_QUERY.
Путаница между удалением свойства (NULL) и установкой значения JSON null ('null').
DECLARE @json NVARCHAR(MAX) = N'{"name": "Иван"}'
SELECT JSON_MODIFY(@json, '$.test', NULL) AS Remove,
JSON_MODIFY(@json, '$.test', 'null') AS SetNull;Remove: {"name": "Иван"}
SetNull: {"name": "Иван", "test": null}Использование индекса массива вне границ может не привести к ошибке, но и не добавит элемент. Для добавления в конец массива используется путь с индексом, равным длине массива, или специальный синтаксис 'append'.
DECLARE @json NVARCHAR(MAX) = N'{"arr": [1,2]}'
SELECT JSON_MODIFY(@json, '$.arr[5]', 10); -- Индекс 5, а длина массива 2{"arr": [1,2]}Изменения в последних версиях
В SQL Server 2017 была добавлена поддержка флага 'append' для добавления элементов в конец массива. До этого требовалось знать длину массива или использовать обходные пути. Синтаксис: JSON_MODIFY(expression, 'append $.path', newValue).
DECLARE @json NVARCHAR(MAX) = N'{"items": ["яблоко", "банан"]}'
SELECT JSON_MODIFY(@json, 'append $.items', 'апельсин') AS UpdatedJson;{"items": ["яблоко", "банан", "апельсин"]}Начиная с SQL Server 2016 (где появилась поддержка JSON) и до текущих версий, основные возможности функции остаются стабильными. Дополнительные улучшения связаны с общей производительностью обработки JSON и интеграцией с другими компонентами SQL Server.
Расширенные и специальные примеры
Использование JSON_QUERY для вставки объекта или массива без экранирования:
DECLARE @json NVARCHAR(MAX) = N'{"user": {"name": "Иван"}}'
DECLARE @newAddress NVARCHAR(MAX) = N'{"city": "Москва", "street": "Ленина"}'
-- Без JSON_QUERY строка будет экранирована как обычная строка
SELECT JSON_MODIFY(@json, '$.user.address', JSON_QUERY(@newAddress)) AS UpdatedJson;{"user": {"name": "Иван", "address": {"city": "Москва", "street": "Ленина"}}}Множественные изменения с помощью вложенных вызовов JSON_MODIFY:
DECLARE @json NVARCHAR(MAX) = N'{}'
SET @json = JSON_MODIFY(@json, '$.name', N'Иван');
SET @json = JSON_MODIFY(@json, '$.age', 30);
SET @json = JSON_MODIFY(@json, '$.active', 'true');
SELECT @json AS Result;{"name": "Иван", "age": 30, "active": true}Работа с вложенными структурами и создание промежуточных объектов:
DECLARE @json NVARCHAR(MAX) = N'{}'
-- Создаем объект contact, а внутри него свойства phone и email
SET @json = JSON_MODIFY(@json, '$.contact.phone', '+71234567890');
-- Первый вызов не создаст contact, потому что путь сложный. Нужно сначала создать объект contact.
SET @json = JSON_MODIFY(@json, '$.contact', JSON_QUERY('{}'));
SET @json = JSON_MODIFY(@json, '$.contact.phone', '+71234567890');
SET @json = JSON_MODIFY(@json, '$.contact.email', 'test@example.com');
SELECT @json AS Result;{"contact": {"phone": "+71234567890", "email": "test@example.com"}}Изменение типа данных значения. JSON_MODIFY автоматически преобразует типы SQL в JSON:
DECLARE @json NVARCHAR(MAX) = N'{}'
SELECT JSON_MODIFY(@json, '$.int', 42) AS IntValue,
JSON_MODIFY(@json, '$.float', 3.14) AS FloatValue,
JSON_MODIFY(@json, '$.bool', 'true') AS BoolValue,
JSON_MODIFY(@json, '$.date', GETDATE()) AS DateValue;IntValue: {"int": 42}
FloatValue: {"float": 3.14}
BoolValue: {"bool": true}
DateValue: {"date": "2023-10-05T12:34:56.789"}Обработка массивов: добавление, удаление, замена нескольких элементов. Использование 'append' для добавления в массив:
DECLARE @json NVARCHAR(MAX) = N'{"ids": [1,2,3]}'
-- Удаляем второй элемент (индекс 1)
SET @json = JSON_MODIFY(@json, '$.ids[1]', NULL)
-- Добавляем новый элемент в конец
SET @json = JSON_MODIFY(@json, 'append $.ids', 5)
SELECT @json AS Result;{"ids": [1,3,5]}Модификация JSON, хранящегося в таблице, с помощью UPDATE:
CREATE TABLE #Users (Id INT, JsonData NVARCHAR(MAX))
INSERT INTO #Users VALUES (1, N'{"name": "Иван", "visits": 5}')
UPDATE #Users
SET JsonData = JSON_MODIFY(JsonData, '$.visits', 6)
WHERE Id = 1
SELECT * FROM #Users;Id | JsonData
1 | {"name": "Иван", "visits": 6}Аналоги в других СУБД и языках
MySQL: Функция JSON_SET(), JSON_INSERT(), JSON_REPLACE(). JSON_SET() добавляет или обновляет значения. Удаление осуществляется функцией JSON_REMOVE().
SET @json = '{"name": "Иван", "age": 30}';
SELECT JSON_SET(@json, '$.age', 31, '$.city', 'Москва');{"name": "Иван", "age": 31, "city": "Москва"}PostgreSQL: Операторы и функции для работы с JSONB, такие как jsonb_set. Синтаксис отличается, поддержка операторов более богатая.
SELECT jsonb_set('{"name": "Иван", "age": 30}'::jsonb, '{age}', '31');{"age": 31, "name": "Иван"}Oracle: Функция json_transform для модификации. Также есть json_mergepatch. Синтаксис более многословный.
SELECT JSON_TRANSFORM('{"name": "Иван", "age": 30}', SET '$.age' = 31) FROM dual;{"name":"Иван","age":31}SQLite: Функция json_set из расширения JSON1. Поведение аналогично MySQL.
SELECT json_set('{"name": "Иван", "age": 30}', '$.age', 31);{"name":"Иван","age":31}В отличие от MS SQL, во многих СУБД функции для модификации JSON часто разделены на несколько (для вставки, замены, удаления), а не объединены в одну с особыми правилами для NULL.