JSONB_SET in PostgreSQL: Patch a Single Field Inside a JSON Document

Представьте: у пользователя сменился город, а его профиль — это пухлый JSON на полтора десятка ключей. Тянуть весь документ в приложение, править одно поле и записывать обратно — расточительно и чревато гонками: между чтением и записью кто-то ещё успеет поменять соседнее поле, и его правка молча затрётся. jsonb_set решает задачу иначе: он возвращает копию документа, в которой значение по указанному пути заменено новым, а всё остальное остаётся на месте. Это рабочая лошадка точечных обновлений — меняешь одно вложенное поле прямо в базе, не вытаскивая строку наружу и не задевая соседние ключи. Дальше разберём сигнатуру, коварный флаг create_missing, типичный UPDATE и отдельный инструмент для удаления.

Синтаксис и путь к значению

Сигнатура лаконична: jsonb_set(target, path, new_value [, create_missing]). Первый аргумент — сам документ, третий — новое значение, а сердце всей затеи — это path: массив текстовых ключей, который шаг за шагом спускается к нужному месту в документе. Если представить JSON как дерево, то путь — это маршрут от корня до листа, который вы собираетесь заменить.

SELECT jsonb_set(
    '{"address": {"city": "Quito", "zip": "170150"}}'::jsonb,
    '{address,city}',
    '"Lima"'
);
-- {"address": {"city": "Lima", "zip": "170150"}}

Несколько моментов, на которых легко споткнуться:

• path пишется как литерал массива: '{address,city}' читается «ключ address, а внутри него ключ city».
• new_value обязан быть валидным JSON. Строку "Lima" пишут вместе с кавычками; голое Lima тут же сорвётся в ошибку.
• В путь можно вплести индексы массива: '{tags,0}' — это первый элемент массива tags. Отрицательные индексы отсчитываются с хвоста, так что '{tags,-1}' укажет на последний элемент.
• Функция дружит только с типом jsonb, но не с json. Если столбец объявлен как json, приведите его через ::jsonb — иначе получите ошибку о несовпадении типов.
• Документ не меняется на месте: на выходе — новое значение, а исходник нетронут, пока вы сами не присвоите результат обратно.

Флаг create_missing

Четвёртый аргумент решает судьбу случая, когда нужного ключа в документе ещё нет. По умолчанию он равен true, и тогда отсутствующий ключ просто дописывается. Поставьте false — и функция превратится в строгий «обнови, но не создавай»: если ключа нет, документ вернётся как был. Это удобно, когда вы хотите править только то, что уже заведено, и не плодить случайных полей из опечаток в пути.

-- key "verified" is absent; with create_missing = true (default) it is added
SELECT jsonb_set(
    '{"city": "Lima"}'::jsonb,
    '{verified}',
    'true',
    true
);
-- {"city": "Lima", "verified": true}

-- with create_missing = false the document is returned unchanged
SELECT jsonb_set(
    '{"city": "Lima"}'::jsonb,
    '{verified}',
    'true',
    false
);
-- {"city": "Lima"}

Грабли: create_missing создаёт только последний ключ пути. Если промежуточного объекта не существует (скажем, ключа address вообще нет, а вы пишете в '{address,city}'), путь оказывается недостижимым — и документ возвращается без изменений, без всякой ошибки. Это легко принять за «сработало» и поехать дальше с молчаливым багом, поэтому сверяйтесь с результатом.

Правим вложенное поле в UPDATE

Главный сценарий — поправить одно поле прямо в таблице, не вытаскивая строку наружу. Пусть в users лежит JSONB-столбец profile, и надо сменить город конкретного пользователя:

UPDATE users
SET profile = jsonb_set(profile, '{address,city}', '"Lima"')
WHERE id = 42;

Раз jsonb_set отдаёт копию, мы тут же присваиваем её обратно в столбец — без этого присваивания вычисленное значение просто растворится, ведь сама функция ничего в таблице не меняет. Остальные поля profile остаются ровно такими, какими были: движок переписывает строку целиком, но содержимое соседних ключей не страдает.

Вызовы свободно вкладываются друг в друга — так за один проход меняют несколько полей:

UPDATE users
SET profile = jsonb_set(
        jsonb_set(profile, '{address,city}', '"Lima"'),
        '{verified}', 'true'
    )
WHERE country = 'PE';

С числами держите ухо востро: вставлять нужно само число, без кавычек. jsonb_set(data, '{score}', '10') положит 10, а '"10"' — уже строку "10", и потом сравнения по числу начнут вести себя странно. Динамическое значение удобнее всего собрать через to_jsonb:

UPDATE orders
SET meta = jsonb_set(meta, '{discount}', to_jsonb(amount * 0.1))
WHERE status = 'paid';

Удаляем ключи оператором минус

А вот чтобы выкинуть поле, jsonb_set уже не нужен — заменить значение на null это не то же самое, что удалить ключ, и для настоящего удаления есть оператор -. Он убирает ключ верхнего уровня или элемент массива по индексу.

-- remove a top-level key
SELECT '{"city": "Lima", "tmp": 1}'::jsonb - 'tmp';
-- {"city": "Lima"}

-- remove an array element by index
SELECT '["a", "b", "c"]'::jsonb - 1;
-- ["a", "c"]

Для вложенного ключа берут оператор #- и скармливают ему путь:

UPDATE users
SET profile = profile #- '{address,zip}'
WHERE id = 42;

Несколько ключей верхнего уровня сносят разом, передав массив:

SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{a,b}'::text[];
-- {"c": 3}

Как это в других СУБД

PostgreSQL для такой работы — самый ладный движок: путь как массив, флаг создания ключа и отдельные операторы удаления складываются в стройную систему. Но у соседей синтаксис свой, и переносить запросы дословно не выйдет:

• MySQL обходится JSON_SET(col, '$.address.city', 'Lima'), где путь — это строка вида $.a.b. Рядом живут JSON_REMOVE для удаления и JSON_REPLACE, который никогда не создаёт отсутствующих ключей — по сути, тот же create_missing = false.
• ClickHouse под частичные правки JSON попросту не заточен: его JSON-функции в основном на чтение, а изменить документ обычно означает перезаписать значение целиком.

Если свести всё к одной мысли: в PostgreSQL jsonb_set всегда возвращает новый документ, поэтому живёт только в паре с присваиванием (SET col = jsonb_set(...)), а за удалением идите к - и #-.