Удаление из JSONB в PostgreSQL: операторы - и #- простыми словами

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

Но рано или поздно появляется задача:

нужно убрать из JSONB лишнее поле.

Например:

• удалить временный флаг онбординга;
• не отдавать наружу password_hash;
• вырезать internal_notes перед API-ответом;
• убрать сырой номер карты из лога оплаты;
• удалить один элемент из массива;
• зачистить вложенное поле глубоко внутри документа.

Для этого в PostgreSQL есть два основных оператора:

- удаляет ключ или элемент на верхнем уровне.

#- удаляет значение по вложенному пути.

Оба оператора не меняют исходный документ «на месте». Они возвращают новый jsonb без указанной части. Поэтому их можно использовать двумя способами:

• в SELECT, чтобы временно скрыть поле при выводе;
• в UPDATE, чтобы действительно сохранить очищенный JSONB в таблице.

Почему это удобно

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

CREATE TABLE users (
    id      bigint PRIMARY KEY,
    email   text NOT NULL,
    profile jsonb NOT NULL
);

Внутри profile может лежать такой документ:

{
    "name": "Ann",
    "role": "admin",
    "password_hash": "abc123",
    "onboarding_temp": true
}

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

SELECT
    id,
    email,
    profile - 'password_hash' - 'onboarding_temp' AS public_profile
FROM users
WHERE id = 42;

В таблице данные останутся прежними. Но в результате запроса public_profile уже будет без этих ключей.

Это хороший приём для маскирования на лету.

Оператор - для удаления ключа верхнего уровня

Самый простой случай: удалить ключ из JSONB-объекта.

SELECT '{"name": "Ann", "temp": true}'::jsonb - 'temp' AS result;

Результат:

{
    "name": "Ann"
}

Здесь слева стоит JSONB-документ, а справа — имя ключа, который нужно удалить.

Важно: оператор - в таком виде работает по верхнему уровню документа. Он не спускается внутрь вложенных объектов.

Например:

SELECT '{"user": {"name": "Ann", "temp": true}}'::jsonb - 'temp' AS result;

В этом случае ничего не удалится, потому что ключ temp находится не на верхнем уровне, а внутри объекта user.

Для вложенных полей нужен другой оператор — #-. До него скоро дойдём.

Удаление временного ключа через UPDATE

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

Например, у всех пользователей нужно убрать временный ключ onboarding_temp:

UPDATE users
SET profile = profile - 'onboarding_temp'
WHERE profile ? 'onboarding_temp';

Разберём запрос.

profile - 'onboarding_temp' возвращает новый JSONB без этого ключа.

SET profile = ... записывает новый JSONB обратно в колонку.

WHERE profile ? 'onboarding_temp' отбирает только те строки, где такой ключ действительно есть.

Последняя часть особенно важна. Без WHERE PostgreSQL пройдёт по всем строкам и попытается обновить даже те документы, где удалять нечего. На маленькой таблице это незаметно, а на большой может зря нагрузить базу и индексы.

Если ключа нет, ошибки не будет

Оператор удаления ведёт себя спокойно: если ключ не найден, документ вернётся без изменений.

SELECT '{"name": "Ann"}'::jsonb - 'temp' AS result;

Результат:

{
    "name": "Ann"
}

Это удобно: не нужно каждый раз заранее проверять существование ключа ради безопасности.

Но для массовых UPDATE проверка всё равно полезна, чтобы не переписывать строки впустую.

Оператор - для удаления элемента массива по индексу

Оператор - умеет работать не только с объектами, но и с массивами.

Если справа указать число, PostgreSQL удалит элемент массива по индексу.

Индексация начинается с нуля.

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

Результат:

[
    "a",
    "c"
]

Элемент с индексом 1 — это "b", поэтому он исчез.

Первый элемент удаляется так:

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

Результат:

[
    "b",
    "c"
]

Отрицательный индекс: удаление с конца массива

PostgreSQL поддерживает отрицательные индексы.

-1 означает последний элемент массива, -2 — предпоследний.

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

Результат:

[
    "a",
    "b"
]

Запись выглядит немного непривычно: - -1.

Первый минус — это оператор удаления. Второй минус — знак отрицательного числа.

То есть выражение читается так:

из массива удалить элемент с индексом -1, то есть последний.

Удаление строкового значения из массива

Есть ещё один полезный режим.

Если слева JSONB-массив, а справа текст, оператор - удаляет строковые элементы массива с таким значением.

SELECT '["admin", "ops", "temp"]'::jsonb - 'temp' AS result;

Результат:

[
    "admin",
    "ops"
]

Это удобно, если в JSONB лежит массив ролей, тегов или флагов.

Но важно не путать:

• для объекта строка справа означает имя ключа;
• для массива строка справа означает строковое значение элемента;
• для массива число справа означает индекс элемента.

Удаление сразу нескольких ключей

Если нужно удалить несколько ключей верхнего уровня, можно передать справа массив text[].

Например:

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

Результат:

{
    "b": 2
}

Это удобнее, чем писать цепочку:

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

Оба варианта работают, но text[] читается лучше, когда ключей много.

Пример: скрыть чувствительные поля перед выдачей

Допустим, в профиле пользователя есть внутренние поля:

{
    "name": "Ann",
    "city": "Lisbon",
    "password_hash": "abc123",
    "internal_notes": "vip",
    "ssn": "123-45-6789"
}

Перед выдачей наружу их нужно убрать:

SELECT
    id,
    email,
    profile - '{password_hash,internal_notes,ssn}'::text[] AS public_profile
FROM users
WHERE id = 42;

В результате останется только публичная часть профиля.

Обратите внимание на приведение:

'{password_hash,internal_notes,ssn}'::text[]

Оно важно. Мы явно говорим PostgreSQL: справа массив строк, то есть нужно удалить несколько ключей.

Почему важно приводить к text[]

Литерал вида '{a,c}' сам по себе может быть неоднозначным.

Если написать так:

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

PostgreSQL может воспринять '{a,c}' не как массив ключей, а как один текстовый ключ с именем {a,c}.

Такого ключа в документе нет, поэтому результат останется без изменений.

Правильный вариант:

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

Когда удаляете несколько ключей, всегда явно указывайте ::text[].

Ограничение оператора -: только верхний уровень

Оператор - с ключами работает только на верхнем уровне.

Например:

SELECT '{"user": {"name": "Ann", "secret": "x"}}'::jsonb - 'secret' AS result;

Результат останется таким же:

{
    "user": {
        "name": "Ann",
        "secret": "x"
    }
}

Ключ secret находится внутри user, поэтому обычный - 'secret' его не видит.

Для вложенного удаления нужен оператор #-.

Оператор #- для удаления по вложенному пути

Оператор #- удаляет значение по пути.

Путь передаётся как text[]: каждый элемент массива — это шаг внутрь JSONB-документа.

Пример:

SELECT '{"user": {"name": "Ann", "secret": "x"}}'::jsonb #- '{user,secret}' AS result;

Результат:

{
    "user": {
        "name": "Ann"
    }
}

Путь '{user,secret}' читается так:

1. зайди в ключ user;
2. внутри него удали ключ secret.

Это уже не верхний уровень, а вложенное удаление.

Удаление глубоко вложенного поля

JSONB-документы часто бывают глубже одного уровня.

Например:

{
    "payment": {
        "card": {
            "last4": "1111",
            "raw_number": "4111111111111111"
        }
    }
}

Нужно удалить только сырой номер карты:

SELECT
    '{"payment": {"card": {"last4": "1111", "raw_number": "4111111111111111"}}}'::jsonb
    #- '{payment,card,raw_number}' AS result;

Результат:

{
    "payment": {
        "card": {
            "last4": "1111"
        }
    }
}

Оператор #- аккуратно дошёл до нужного места и удалил только последний элемент пути.

Удаление элемента массива внутри объекта

В пути можно использовать не только ключи объектов, но и индексы массивов.

Например, есть заказ с массивом товаров:

{
    "items": [
        {"sku": "A1", "qty": 1},
        {"sku": "B2", "qty": 2},
        {"sku": "C3", "qty": 1}
    ]
}

Удалим второй элемент массива:

SELECT
    '{"items": [{"sku": "A1", "qty": 1}, {"sku": "B2", "qty": 2}, {"sku": "C3", "qty": 1}]}'::jsonb
    #- '{items,1}' AS result;

Результат:

{
    "items": [
        {
            "qty": 1,
            "sku": "A1"
        },
        {
            "qty": 1,
            "sku": "C3"
        }
    ]
}

Индекс 1 означает второй элемент, потому что массивы нумеруются с нуля.

Удаление поля внутри первого элемента массива

Можно пойти ещё глубже.

Например, есть массив заказов, и в первом заказе нужно удалить поле card:

SELECT
    '{"orders": [{"id": 1, "card": "4111"}, {"id": 2, "card": "5555"}]}'::jsonb
    #- '{orders,0,card}' AS result;

Результат:

{
    "orders": [
        {
            "id": 1
        },
        {
            "id": 2,
            "card": "5555"
        }
    ]
}

Путь '{orders,0,card}' читается так:

1. зайди в orders;
2. возьми элемент массива с индексом 0;
3. удали ключ card.

Пример: зачистка платежных логов

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

CREATE TABLE orders (
    id     bigint PRIMARY KEY,
    status text NOT NULL,
    meta   jsonb NOT NULL
);

Внутри meta может лежать платежная информация:

{
    "payment": {
        "provider": "stripe",
        "raw_card_number": "4111111111111111",
        "status": "approved"
    }
}

Сырой номер карты хранить нельзя. Нужно удалить его из уже сохранённых данных:

UPDATE orders
SET meta = meta #- '{payment,raw_card_number}'
WHERE status = 'paid'
  AND meta #> '{payment,raw_card_number}' IS NOT NULL;

Разберём.

meta #- '{payment,raw_card_number}' возвращает новый JSONB без вложенного поля.

meta #> '{payment,raw_card_number}' IS NOT NULL проверяет, что такое поле действительно есть.

Так мы не трогаем строки, где удалять нечего.

Если путь не найден, ошибки не будет

Как и оператор -, оператор #- не падает с ошибкой, если путь не найден.

SELECT '{"user": {"name": "Ann"}}'::jsonb #- '{user,secret}' AS result;

Результат останется прежним:

{
    "user": {
        "name": "Ann"
    }
}

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

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

Ключи с пробелами, точками и странными символами

В #- путь — это массив текстовых элементов, а не строка в стиле JSONPath.

Это значит, что точка внутри имени ключа не считается переходом на следующий уровень.

Например, есть документ:

{
    "user": {
        "full name": "Ann Smith",
        "profile.version": 3
    }
}

Удалить ключ full name можно так:

SELECT
    '{"user": {"full name": "Ann Smith", "profile.version": 3}}'::jsonb
    #- '{user,full name}' AS result;

Удалить ключ profile.version можно так:

SELECT
    '{"user": {"full name": "Ann Smith", "profile.version": 3}}'::jsonb
    #- '{user,profile.version}' AS result;

Здесь profile.version — это одно имя ключа, а не два шага profile и version.

Это важное отличие от JSONPath-подхода в некоторых других базах.

Число в пути: индекс массива или ключ объекта

В пути для #- элементы записываются как текст.

Например:

'{items,0,name}'

Если текущий узел — массив, 0 будет воспринят как индекс массива.

Если текущий узел — объект, PostgreSQL будет искать ключ с именем 0.

Поэтому важно понимать структуру документа.

Например:

SELECT '{"0": "zero", "1": "one"}'::jsonb #- '{0}' AS result;

Здесь удаляется ключ "0" из объекта.

А здесь:

SELECT '["zero", "one"]'::jsonb #- '{0}' AS result;

Удаляется первый элемент массива.

Один и тот же текстовый шаг пути работает по-разному в зависимости от того, объект сейчас перед нами или массив.

Удаление не меняет документ без UPDATE

Очень важная мысль для новичков:

- и #- возвращают новый JSONB, но сами по себе не сохраняют его в таблицу.

Например:

SELECT profile - 'password_hash'
FROM users
WHERE id = 42;

Этот запрос только покажет профиль без password_hash.

Но в таблице profile останется прежним.

Чтобы сохранить изменение, нужен UPDATE:

UPDATE users
SET profile = profile - 'password_hash'
WHERE id = 42;

То же самое с вложенным удалением:

UPDATE orders
SET meta = meta #- '{payment,raw_card_number}'
WHERE id = 100;

Запомните простое правило:

• SELECT — показать изменённую копию;
• UPDATE — записать изменённую копию в таблицу.

Удаление нескольких вложенных полей

Оператор #- удаляет один узел по одному пути.

Если нужно удалить несколько вложенных полей, можно соединить несколько вызовов в цепочку.

Например:

UPDATE orders
SET meta = meta
    #- '{payment,raw_card_number}'
    #- '{payment,cvv}'
    #- '{debug,trace_id}'
WHERE id = 100;

Запрос читается слева направо:

1. из meta удалили payment.raw_card_number;
2. из результата удалили payment.cvv;
3. из результата удалили debug.trace_id;
4. итог записали обратно в meta.

Это нормальная практика, если путей немного.

Связка с jsonb_set

Иногда нужно не только удалить поле, но и сразу поставить новое.

Например:

• удалить cvv;
• добавить флаг redacted;
• сохранить всё одним UPDATE.

Для этого можно совместить #- и jsonb_set.

UPDATE orders
SET meta = jsonb_set(
    meta #- '{payment,cvv}',
    '{redacted}',
    'true'::jsonb
)
WHERE id = 100;

Что происходит:

meta #- '{payment,cvv}' удаляет вложенное поле.

jsonb_set(..., '{redacted}', 'true'::jsonb) добавляет или обновляет ключ redacted.

В итоге в таблицу попадёт уже очищенный и помеченный документ.

Маскирование в SELECT: безопасный ответ наружу

Один из лучших сценариев для операторов удаления — подготовка публичной версии JSONB.

Например, внутри profile есть и публичные, и внутренние поля:

{
    "name": "Ann",
    "country": "ES",
    "password_hash": "abc123",
    "internal_notes": "manual review",
    "flags": {
        "risk_score": 80,
        "onboarding_temp": true
    }
}

Для API можно убрать верхнеуровневые секреты и вложенный технический флаг:

SELECT
    id,
    email,
    (profile - '{password_hash,internal_notes}'::text[])
        #- '{flags,onboarding_temp}' AS public_profile
FROM users
WHERE id = 42;

Такой запрос не меняет таблицу, а только формирует безопасный результат.

Это удобно, когда вы хотите оставить внутренние данные в базе, но не показывать их клиенту.

Производительность и большие UPDATE

Если JSONB-колонка большая, а таблица содержит много строк, массовое удаление полей может быть дорогим.

Например:

UPDATE users
SET profile = profile - 'onboarding_temp';

Такой запрос попытается обновить все строки.

Даже если у части строк нет ключа onboarding_temp, база всё равно будет выполнять работу. А если по JSONB-колонке есть GIN-индекс, его тоже придётся обновлять.

Лучше писать точнее:

UPDATE users
SET profile = profile - 'onboarding_temp'
WHERE profile ? 'onboarding_temp';

Для вложенного пути:

UPDATE orders
SET meta = meta #- '{payment,raw_card_number}'
WHERE meta #> '{payment,raw_card_number}' IS NOT NULL;

Так база обновит только те строки, где действительно есть что удалять.

Разница между - и #-

Коротко разница такая:

Оператор	Что удаляет	Где работает
- 'key'	ключ объекта или строку из массива	верхний уровень
- 1	элемент массива по индексу	верхний уровень
- '{a,b}'::text[]	несколько ключей или строковых элементов	верхний уровень
#- '{a,b,c}'	значение по вложенному пути	любая глубина

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

Если нужно залезть внутрь документа — используйте #-.

Аналог в MySQL

В MySQL ближайший аналог — функция JSON_REMOVE.

Пример:

SELECT JSON_REMOVE(doc, '$.payment.raw_card_number')
FROM orders;

Ей можно передать несколько путей:

SELECT JSON_REMOVE(doc, '$.payment.raw_card_number', '$.payment.cvv')
FROM orders;

Смысл похожий: удалить часть JSON-документа по пути.

Но синтаксис другой. В PostgreSQL используются операторы - и #-, а путь для #- передаётся как text[], например '{payment,cvv}'.

А что в ClickHouse

В ClickHouse подход обычно другой.

Это колоночная аналитическая база, и JSON-данные там чаще разбирают при чтении, складывают в отдельные колонки или пересобирают при загрузке. Прямого аналога PostgreSQL-операторов - и #- для удобного изменения JSONB-документа в строке обычно не используют.

Если нужно «удалить поле», чаще делают новую версию данных: читают исходное значение, пересобирают нужную структуру и записывают результат заново.

Поэтому при переносе запросов важно помнить: PostgreSQL умеет удобно изменять JSONB-выражение прямо в SQL, но в других СУБД это может решаться совсем иначе.

Частые ошибки

Первая ошибка — думать, что SELECT profile - 'secret' меняет таблицу.

Не меняет. Он только показывает новую версию документа в результате запроса. Для сохранения нужен UPDATE.

Вторая ошибка — пытаться удалить вложенный ключ обычным -.

SELECT '{"user": {"secret": "x"}}'::jsonb - 'secret' AS result;

Так ключ не удалится, потому что он не на верхнем уровне. Нужен #-:

SELECT '{"user": {"secret": "x"}}'::jsonb #- '{user,secret}' AS result;

Третья ошибка — забывать ::text[] при удалении нескольких ключей.

Правильно:

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

Четвёртая ошибка — делать массовый UPDATE без фильтра.

Лучше сначала проверить, есть ли нужный ключ или путь:

WHERE profile ? 'onboarding_temp'

или:

WHERE meta #> '{payment,raw_card_number}' IS NOT NULL

Главное из статьи

В PostgreSQL для удаления частей JSONB чаще всего используют два оператора: - и #-.

Оператор - работает по верхнему уровню документа.

Он может удалить ключ объекта:

SELECT '{"name": "Ann", "temp": true}'::jsonb - 'temp' AS result;

Он может удалить элемент массива по индексу:

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

Он может удалить несколько ключей, если справа передать text[]:

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

Оператор #- удаляет значение по вложенному пути:

SELECT '{"user": {"name": "Ann", "secret": "x"}}'::jsonb #- '{user,secret}' AS result;

Если ключ или путь не найден, ошибки не будет: PostgreSQL вернёт документ без изменений.

- и #- сами по себе не сохраняют изменения в таблицу. Они только возвращают новый jsonb. Чтобы изменение осталось в базе, нужен UPDATE.

Для массовых обновлений добавляйте точный WHERE, чтобы не переписывать строки, где удалять нечего.

Главное правило простое:

используйте - для верхнего уровня, #- для вложенных путей, а для постоянного удаления всегда записывайте результат обратно через UPDATE.