Оператор || для jsonb в PostgreSQL: как быстро объединять JSON-документы

В PostgreSQL у типа jsonb есть удобный оператор ||. Он позволяет объединять JSON-документы прямо в SQL: добавить новое поле, обновить существующее, дописать элемент в массив или собрать «обогащённую» версию объекта для отчёта.

Представьте, что в таблице заказов есть JSON-поле с дополнительной информацией:

{
  "source": "mobile",
  "paid": false
}

Заказ оплатили, и теперь хочется добавить способ оплаты:

{
  "source": "mobile",
  "paid": true,
  "method": "card"
}

Можно выгрузить весь JSON в приложение, разобрать его там, изменить и отправить обратно. Но часто это лишняя работа. В PostgreSQL можно сделать коротко и прямо в базе:

UPDATE orders
SET meta = meta || '{"paid": true, "method": "card"}'::jsonb
WHERE id = 1001;

Оператор || выглядит простым, но у него есть важное правило:

|| делает поверхностное слияние. Он работает только на верхнем уровне JSON-документа.

Это значит: верхние ключи объединяются, правые значения перезаписывают левые, а вложенные объекты не сливаются аккуратно внутри. Если не помнить это правило, можно случайно затереть часть данных.

Разберём всё по порядку.

Что делает оператор ||

Оператор || у jsonb объединяет два значения.

Его поведение зависит от того, что именно вы объединяете:

• объект с объектом — получится один объект;
• массив с массивом — получится один общий массив;
• массив со скаляром — скаляр добавится как отдельный элемент;
• объект со скаляром или массивом — PostgreSQL тоже приведёт значения к массиву и объединит их.

На практике чаще всего используют три сценария:

1. Объединить два JSON-объекта.
2. Дописать элемент в JSON-массив.
3. Обновить jsonb-поле в UPDATE.

Слияние объектов: правый ключ побеждает

Когда оба значения — JSON-объекты, PostgreSQL объединяет их ключи.

Если ключ есть только слева, он останется. Если ключ есть только справа, он добавится. Если ключ есть и слева, и справа, победит значение справа.

SELECT
    '{"verified": false, "city": "Lima"}'::jsonb
    || '{"verified": true}'::jsonb AS result;

Результат:

{"city": "Lima", "verified": true}

Ключ city остался из левого объекта. Ключ verified был в обоих объектах, поэтому значение справа заменило значение слева.

Было:

{"verified": false, "city": "Lima"}

Наложили сверху:

{"verified": true}

Получили:

{"city": "Lima", "verified": true}

Это похоже на наклейку поверх старой карточки: всё старое остаётся на месте, но если новая наклейка закрыла конкретное поле, видно уже новое значение.

Порядок операндов важен

a || b и b || a — не одно и то же, если ключи пересекаются.

SELECT
    '{"role": "user"}'::jsonb || '{"role": "admin"}'::jsonb AS first_result,
    '{"role": "admin"}'::jsonb || '{"role": "user"}'::jsonb AS second_result;

Результат будет разным:

first_result  -> {"role": "admin"}
second_result -> {"role": "user"}

Последнее слово всегда за правым объектом.

Это особенно важно в UPDATE. Патч обычно ставят справа:

UPDATE users
SET profile = profile || '{"verified": true}'::jsonb
WHERE id = 42;

Так старый profile берётся за основу, а новый кусочек JSON накладывается сверху.

Оператор возвращает новый jsonb, а не меняет старый на месте

|| не «лезет внутрь» старого значения и не мутирует его прямо на месте. Он возвращает новый jsonb.

Поэтому в UPDATE мы пишем так:

UPDATE users
SET profile = profile || '{"verified": true}'::jsonb
WHERE id = 42;

То есть мы берём старый profile, создаём на его основе новый JSON-документ и записываем его обратно в столбец.

Оба значения должны быть jsonb

Оператор || работает именно с jsonb.

Если вы пишете JSON как строку, его нужно явно привести к jsonb:

SELECT '{"name": "Alex"}'::jsonb || '{"active": true}'::jsonb AS result;

Если столбец в таблице имеет тип jsonb, всё хорошо:

UPDATE users
SET profile = profile || '{"active": true}'::jsonb
WHERE id = 42;

А если столбец почему-то имеет тип json, его нужно привести:

UPDATE users
SET profile = profile::jsonb || '{"active": true}'::jsonb
WHERE id = 42;

Но обычно для активной работы в PostgreSQL удобнее хранить документы именно в jsonb, а не в json.

JSON null не удаляет ключ

Важный момент: если справа передать ключ со значением null, PostgreSQL не удалит этот ключ. Он просто запишет туда JSON-значение null.

SELECT
    '{"city": "Lima", "verified": true}'::jsonb
    || '{"verified": null}'::jsonb AS result;

Результат:

{"city": "Lima", "verified": null}

Ключ verified не исчез. Он остался, просто теперь его значение равно null.

Это не то же самое, что удалить ключ из объекта. Для удаления ключей в PostgreSQL используют другие инструменты, например оператор -.

SELECT
    '{"city": "Lima", "verified": true}'::jsonb - 'verified' AS result;

Результат:

{"city": "Lima"}

Запомните разницу:

• || '{"key": null}'::jsonb — оставляет ключ и записывает туда null;
• - 'key' — удаляет ключ из объекта.

Конкатенация массивов

Если оба значения — JSON-массивы, оператор || склеивает их.

SELECT
    '["sql", "json"]'::jsonb || '["postgres"]'::jsonb AS result;

Результат:

["sql", "json", "postgres"]

Это удобно, когда в JSON-документе хранятся теги, роли, отметки, флаги или другие списки.

Например, у пользователя есть массив тегов:

{"tags": ["sql", "backend"]}

И мы хотим добавить тег postgres.

Но здесь есть тонкость. Если массив лежит внутри объекта, простого || на весь объект будет недостаточно для аккуратного добавления внутрь массива. Такой запрос заменит весь ключ tags:

SELECT
    '{"tags": ["sql", "backend"]}'::jsonb
    || '{"tags": ["postgres"]}'::jsonb AS result;

Результат:

{"tags": ["postgres"]}

Старые теги пропали, потому что || увидел совпадающий верхний ключ tags и заменил его целиком.

Чтобы именно дописать элемент во вложенный массив, обычно берут текущее значение массива, добавляют к нему новый элемент и записывают обратно через jsonb_set.

UPDATE users
SET profile = jsonb_set(
    profile,
    '{tags}',
    COALESCE(profile -> 'tags', '[]'::jsonb) || '["postgres"]'::jsonb
)
WHERE id = 42;

Здесь происходит три шага:

1. profile -> 'tags' достаёт текущий массив тегов.
2. COALESCE(..., '[]'::jsonb) подставляет пустой массив, если тегов ещё нет.
3. jsonb_set записывает новый массив обратно по пути tags.

Так старые элементы не теряются.

Если массив соединить со скаляром

PostgreSQL умеет склеивать массив не только с массивом.

Если один операнд — массив, а второй — обычное JSON-значение, это значение добавится как отдельный элемент.

SELECT
    '["a", "b"]'::jsonb || '"c"'::jsonb AS result;

Результат:

["a", "b", "c"]

Обратите внимание на кавычки вокруг "c":

'"c"'::jsonb

Внешние кавычки нужны SQL-строке, а внутренние — JSON-строке. Без внутренних кавычек это был бы невалидный JSON.

Можно добавить и объект:

SELECT
    '["a", "b"]'::jsonb || '{"code": "c"}'::jsonb AS result;

Результат:

["a", "b", {"code": "c"}]

То есть объект стал одним элементом массива.

Частичный патч в UPDATE

Самый частый практический сценарий — обновить несколько полей внутри jsonb-столбца.

Допустим, есть таблица заказов:

SELECT id, meta
FROM orders
WHERE id = 1001;

В meta лежит такой документ:

{"source": "mobile", "paid": false}

После оплаты хотим записать:

{"source": "mobile", "paid": true, "method": "card"}

Делаем патч:

UPDATE orders
SET meta = meta || '{"paid": true, "method": "card"}'::jsonb
WHERE id = 1001;

Это читается почти как обычная фраза:

Возьми старый meta и наложи на него новые поля.

Если ключа method раньше не было, он добавится. Если paid был равен false, он станет true.

Не собирайте JSON руками через строки

Иногда значения для патча приходят из обычных колонок таблицы.

Например, у пользователя есть отдельные поля country и email, а мы хотим добавить их в profile.

Плохая идея — собирать JSON через склеивание строк. Там легко ошибиться с кавычками, экранированием и типами.

Лучше использовать jsonb_build_object.

UPDATE users
SET profile = profile || jsonb_build_object(
    'country', country,
    'email', email
)
WHERE id = 42;

jsonb_build_object сам соберёт корректный JSONB-объект. Если в email есть кавычки, пробелы или другие специальные символы, PostgreSQL обработает их нормально.

Можно добавлять и вычисляемые значения:

UPDATE users
SET profile = profile || jsonb_build_object(
    'is_active', status = 'active',
    'updated_by', 'system'
)
WHERE id = 42;

Здесь status = 'active' превратится в JSON-значение true или false.

Обогащение JSON в SELECT

Оператор || полезен не только в UPDATE.

Иногда нужно не менять данные в таблице, а просто показать JSON с дополнительными вычисленными полями.

Например, есть профиль пользователя, а рядом обычная колонка status. Для ответа API хотим вернуть профиль плюс поле active.

SELECT
    id,
    profile || jsonb_build_object('active', status = 'active') AS enriched_profile
FROM users;

Таблица при этом не меняется. Мы просто получаем новую версию JSON в результате запроса.

Это удобно для:

• API-ответов;
• отчётов;
• временных витрин;
• проверки будущего патча перед UPDATE.

Перед массовым UPDATE сначала сделайте SELECT

С jsonb легко сделать красивый короткий запрос, но массовое обновление лучше не запускать вслепую.

Перед UPDATE полезно посмотреть рядом:

• исходный документ;
• патч;
• будущий результат.

Например:

SELECT
    id,
    profile AS old_profile,
    '{"verified": true}'::jsonb AS patch,
    profile || '{"verified": true}'::jsonb AS new_profile
FROM users
WHERE registered_at >= DATE '2025-01-01';

Так вы заранее увидите, что именно будет записано.

Особенно это важно, если внутри документа есть вложенные объекты. Именно там чаще всего и происходит неприятная потеря данных.

Главная ловушка: вложенные объекты заменяются целиком

Самая частая ошибка — ждать от || глубокого слияния.

Кажется логичным: если внутри есть объект address, а мы передали новый address только с городом, PostgreSQL должен обновить только город.

Но он так не делает.

SELECT
    '{"address": {"city": "Lima", "zip": "15001"}}'::jsonb
    || '{"address": {"city": "Quito"}}'::jsonb AS result;

Результат:

{"address": {"city": "Quito"}}

Ключ zip пропал.

Почему? Потому что для оператора || ключ address — это просто значение верхнего уровня. Он не заглядывает внутрь и не сравнивает поля city и zip. Он видит:

Слева:

"address": {"city": "Lima", "zip": "15001"}

Справа:

"address": {"city": "Quito"}

Ключ одинаковый, значит побеждает правое значение целиком.

Итог: старый объект address полностью заменён новым объектом address.

Как правильно менять вложенное поле

Если нужно изменить одно поле внутри вложенного объекта, используйте jsonb_set.

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

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

Путь записывается так:

'{address,city}'

Это значит:

зайди в ключ address, а внутри него измени ключ city.

Новое значение тоже должно быть JSONB. Поэтому строка Quito передаётся как JSON-строка:

'"Quito"'

После такого обновления документ станет таким:

{"address": {"city": "Quito", "zip": "15001"}}

Индекс останется на месте.

Правило простое:

• || — для плоских патчей верхнего уровня;
• jsonb_set — для точечной правки внутри документа.

Можно совместить || и jsonb_set

Иногда нужно и верхние поля обновить, и вложенное поле поправить.

Например:

• поставить verified = true;
• изменить address.city.

Можно сделать это в одном выражении:

UPDATE users
SET profile = jsonb_set(
    profile || '{"verified": true}'::jsonb,
    '{address,city}',
    '"Quito"'
)
WHERE id = 42;

Сначала применяется плоский патч:

profile || '{"verified": true}'::jsonb

Потом внутри получившегося документа меняется город:

jsonb_set(..., '{address,city}', '"Quito"')

Так запрос остаётся компактным, но вы не теряете вложенные поля.

Как создать ключ, если его ещё нет

У jsonb_set есть дополнительный параметр: создавать ли отсутствующий ключ.

По умолчанию он равен true, но для читаемости его иногда указывают явно.

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

Но здесь есть важная граница: jsonb_set может создать последний ключ в пути, если родительский объект уже существует.

Если у пользователя вообще нет address, такой путь может не сработать так, как вы ожидаете. В таких случаях сначала создают родительский объект или используют более аккуратную логику.

Например, можно сначала гарантировать наличие address:

UPDATE users
SET profile = jsonb_set(
    profile || '{"address": {}}'::jsonb,
    '{address,city}',
    '"Quito"',
    true
)
WHERE id = 42;

Но помните: если в profile уже был address, выражение profile || '{"address": {}}'::jsonb заменит его пустым объектом. Поэтому такой вариант безопасен только когда вы точно знаете, что address отсутствует.

Для реальных данных лучше проверять результат через SELECT перед массовым обновлением.

Когда || подходит идеально

Оператор || отлично подходит, когда вы обновляете верхний уровень JSON-документа.

Например, добавить флаги:

UPDATE orders
SET meta = meta || '{"paid": true, "notified": false}'::jsonb
WHERE id = 1001;

Добавить техническую информацию:

UPDATE events
SET payload = payload || jsonb_build_object(
    'processed_at', NOW(),
    'processor', 'worker-1'
)
WHERE id = 500;

Собрать ответ для API:

SELECT
    id,
    profile || jsonb_build_object(
        'active', status = 'active',
        'orders_count', orders_count
    ) AS response_body
FROM users;

Подготовить временную витрину:

SELECT
    id,
    meta || '{"source_group": "paid"}'::jsonb AS report_meta
FROM orders
WHERE source IN ('ads', 'partner');

В этих примерах мы работаем с верхним уровнем документа. Поэтому || подходит хорошо.

Когда || лучше не использовать

|| лучше не использовать, если вы хотите:

• изменить одно поле глубоко внутри объекта;
• аккуратно объединить вложенные объекты;
• удалить ключ через null;
• добавить элемент во вложенный массив без риска потерять старые элементы;
• сделать сложный рекурсивный merge.

Во всех этих случаях нужен другой инструмент:

• jsonb_set — для изменения значения по пути;
• - — для удаления ключа;
• #- — для удаления по вложенному пути;
• ручная логика — для сложного глубокого объединения.

Пример удаления вложенного ключа:

SELECT
    '{"address": {"city": "Lima", "zip": "15001"}}'::jsonb
    #- '{address,zip}' AS result;

Результат:

{"address": {"city": "Lima"}}

Отличие от MySQL

В MySQL оператор || не используется для слияния JSON. В зависимости от настроек и контекста он обычно связан с логическим OR, а не с JSON-документами.

Для JSON-слияния в MySQL есть функции, например JSON_MERGE_PATCH.

Но поведение там отличается от PostgreSQL.

В PostgreSQL:

SELECT
    '{"city": "Lima"}'::jsonb || '{"city": null}'::jsonb AS result;

Результат:

{"city": null}

Ключ остаётся.

В логике JSON_MERGE_PATCH значение null в патче обычно означает удаление ключа. То есть при переносе логики из PostgreSQL в MySQL можно получить другой результат.

Есть ещё JSON_MERGE_PRESERVE: она не просто заменяет совпадающие значения, а может сохранять их, складывая в массивы. Это тоже отличается от привычного поведения jsonb || в PostgreSQL.

Главная мысль: не переносите JSON-патчи между СУБД механически. У PostgreSQL, MySQL и других движков разные правила слияния.

А что в ClickHouse

ClickHouse обычно используют не для точечной правки JSON-документов, а для аналитики и быстрого чтения данных.

JSON-функции в ClickHouse в основном помогают доставать значения из JSON, разбирать их и использовать в запросах. Частичное обновление JSON в стиле PostgreSQL там не является таким же привычным рабочим сценарием.

На практике изменение JSON-документа часто сводится к пересборке и перезаписи всего значения или к другой схеме хранения данных.

Поэтому jsonb || — это именно удобство PostgreSQL. Если пишете переносимый SQL, рассчитывать на такой же оператор в других СУБД не стоит.

Практическое правило

Запомните коротко:

jsonb || — это быстрый поверхностный патч.

Он хорош, когда нужно добавить или заменить поля верхнего уровня:

UPDATE orders
SET meta = meta || '{"paid": true, "method": "card"}'::jsonb
WHERE id = 1001;

Он хорош для конкатенации массивов:

SELECT '["sql"]'::jsonb || '["postgres"]'::jsonb AS result;

Но он не делает глубокое слияние:

SELECT
    '{"address": {"city": "Lima", "zip": "15001"}}'::jsonb
    || '{"address": {"city": "Quito"}}'::jsonb AS result;

Результат:

{"address": {"city": "Quito"}}

Если нужно изменить вложенное поле, берите jsonb_set:

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

Главное

Оператор || для jsonb в PostgreSQL нужен для объединения JSONB-значений.

Если объединяются два объекта, PostgreSQL делает поверхностное слияние:

SELECT
    '{"a": 1, "b": 2}'::jsonb || '{"b": 3, "c": 4}'::jsonb AS result;

Результат:

{"a": 1, "b": 3, "c": 4}

Если ключи совпадают, побеждает правый операнд.

Если объединяются массивы, они склеиваются:

SELECT
    '["a", "b"]'::jsonb || '["c"]'::jsonb AS result;

Результат:

["a", "b", "c"]

|| удобно использовать для плоских патчей в UPDATE, особенно вместе с jsonb_build_object.

Но главная ловушка в том, что || не сливает вложенные объекты рекурсивно. Он работает только по верхнему уровню. Если справа пришёл ключ с объектом, этот объект заменит левый целиком.

Поэтому правило такое:

• для верхнего уровня — ||;
• для точечной правки внутри JSON — jsonb_set;
• для удаления ключей — - или #-;
• перед массовым обновлением — сначала проверочный SELECT.

Так вы получите короткие и понятные SQL-запросы и не потеряете данные внутри вложенных JSON-документов.