Deleting from JSONB in PostgreSQL: the - and #- operators

Удаление из JSONB в PostgreSQL — это два оператора, - и #-, которые выкидывают ключ, элемент массива или вложенное значение прямо в SQL, не вытягивая документ в приложение ради пересборки. - работает по верхнему уровню документа, #- — по значению за вложенным путём. Берут их, когда из JSONB надо что-то вынуть перед выдачей наружу: служебный флаг с онбординга, поле с хешем пароля, сырой номер карты, забредший в лог оплаты. Оба не правят оригинал на месте, а возвращают новый jsonb, поэтому их одинаково удобно ставить и прямо в SELECT для маскирования на лету, и в UPDATE для постоянной зачистки.

Оператор -: ключ или индекс массива

Левый операнд здесь — сам jsonb, а правый сообщает, что именно вырезать. Передали строку — улетает ключ верхнего уровня; передали целое число — выпадает элемент массива по этому индексу, причём отрицательный индекс отсчитывается с конца. Тип правого операнда и решает, в каком режиме сработает оператор: для объекта осмысленна только строка-ключ, для массива — число; перепутаете — получите либо документ без изменений, либо ошибку приведения типов. Удаление по индексу всегда нумеруется с нуля, так что - 0 снимает первый элемент, а - -1 — последний.

-- drop a single top-level key
SELECT '{"name": "Ann", "temp": true}'::jsonb - 'temp';
-- => {"name": "Ann"}

-- remove an array element by index (zero-based)
SELECT '["a", "b", "c"]'::jsonb - 1;
-- => ["a", "c"]

-- negative index counts from the end
SELECT '["a", "b", "c"]'::jsonb - -1;
-- => ["a", "b"]

Живой случай: в таблице users колонка profile jsonb с настройками, и нужно одним махом убрать у всех временный ключ.

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

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

Когда справа стоит массив text[], тот же - за один проход вычищает все перечисленные ключи верхнего уровня. Это спасает там, где документ надо причесать перед сериализацией в ответ API: не городить цепочку из пяти вычитаний - 'a' - 'b' - 'c', а перечислить лишнее одним списком ключей.

-- strip several keys at once
SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{a,c}'::text[];
-- => {"b": 2}

-- hide sensitive fields before returning a user row
SELECT id, email,
       profile - '{password_hash,internal_notes,ssn}'::text[] AS public_profile
FROM users
WHERE id = 42;

И сразу важная оговорка: список ключей бьёт только по верхнему уровню, до вложенных полей он не дотягивается — за ними придётся идти с #-. Зато за лишние имена переживать не нужно: отсутствующих ключей оператор не замечает, ошибки нет, а присутствующие удаляются. И ещё тонкость: правый операнд тут именно text[], поэтому литерал '{a,c}' обязательно приводите к text[] явно. Без приведения это строковая константа неизвестного типа, и PostgreSQL сопоставит её с перегрузкой jsonb - text — удалением одного ключа по имени. Ключа {a,c} в документе нет, так что вернётся исходный документ без изменений: не «удалить элемент по индексу» (для индекса нужен целочисленный правый операнд), а промах мимо всех трёх ключей сразу.

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

#- ждёт справа путь в виде text[] и сносит то, что лежит в самом его конце. Элементы пути — это по очереди имена ключей и индексы массивов, ровно по глубине вложенности. За один вызов оператор снимает один узел в конце пути; чтобы вырезать несколько вложенных полей, сцепляйте вызовы — результат одного на вход следующему. Глубина практически не ограничена, лишь бы каждый промежуточный узел существовал и имел подходящий тип.

-- remove a nested key
SELECT '{"user": {"name": "Ann", "secret": "x"}}'::jsonb #- '{user,secret}';
-- => {"user": {"name": "Ann"}}

-- remove a deep array element: orders[0].card
SELECT '{"orders": [{"id": 1, "card": "4111"}]}'::jsonb #- '{orders,0,card}';
-- => {"orders": [{"id": 1}]}

На практике #- чаще всего наводит порядок в логах — например, в колонке orders.meta jsonb, откуда перед экспортом надо вырезать сырой номер карты из вложенного объекта оплаты.

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

Грабли и связка с jsonb_set

• Несуществующий ключ или путь — не ошибка: оператор молча вернёт документ как есть. Отбирайте строки через ? или #>, иначе нарвётесь на пачку пустых UPDATE, гоняющих строки впустую.
• И -, и #- всегда отдают новый jsonb, ничего не меняя в таблице. Чтобы правка осела на диск, нужен UPDATE ... SET col = col - ....
• Если JSONB-колонку покрывает GIN-индекс, массовый UPDATE потащит за собой его перестройку — на больших таблицах это совсем не бесплатно.
• В пути #- число трактуется как индекс массива. Убедитесь, что узел действительно массив, а не объект с числовым ключом, — иначе элемент не найдётся, и документ останется нетронутым.

Отдельная заноза #- — имена ключей с неудобными символами. Точку, пробел или что угодно ещё в text[]-пути пишут как обычный элемент массива, без точечной нотации $.a.b: оператор сравнивает имена буквально. Поэтому '{user,full name}' спокойно достанет ключ full name — то, на чём спотыкается строковый JSONPath из других СУБД.

Удаление редко ходит в одиночку: чаще оно в паре с jsonb_set — одним выражением выбрасываем лишнее, вторым тут же проставляем новое значение, всё в одном запросе.

-- drop a secret and set a redaction flag in one statement
UPDATE orders
SET meta = jsonb_set(meta #- '{payment,cvv}', '{redacted}', 'true'::jsonb)
WHERE id = 100;

В MySQL ближайший аналог обоих операторов — функция JSON_REMOVE(doc, '$.path'), которой можно передать сразу несколько путей через запятую. В ClickHouse JSON-поля обычно неизменяемы, поэтому удаление выражается не оператором, а через INSERT пересобранного значения. Главное про - и #-: это чистые функции над документом, без UPDATE их работа не сохранится, а фильтр по ? или #> убережёт от пустых перезаписей строк, где удалять нечего.