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

A JSONB document is rarely final: stray keys, temporary flags and secret fields all need to go before the row reaches the outside world. PostgreSQL ships two delete operators — - for the top level and #- for a nested path. Both return a brand-new document and never mutate the original in place.

The - operator: a key or an array index

The left operand is jsonb; the right one says what to drop. A string removes a top-level key; an integer removes an array element by index, where a negative index counts from the end.

-- 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"]

Suppose users has a profile jsonb column of settings. Drop a temporary key everywhere:

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

Deleting several keys at once

When the right operand is a text[] array, - removes every listed top-level key in a single pass. That is handy for sanitizing a document before serializing it into an API response.

-- 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;

Note: the array form only touches the top level. It will not reach nested fields — for those you need #-.

The #- operator: a value at a nested path

#- takes a path on the right as a text[] and removes whatever sits at the end of that path. Path elements are key names and array indexes, in nesting order.

-- 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}]}

In practice #- often scrubs log data in orders.meta jsonb before an export:

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

Gotchas and pairing with jsonb_set

• A missing key or path is not an error: the operator simply returns the document unchanged. Filter with ? or #> so you do not fire empty UPDATEs.
• - and #- always return a new jsonb. To persist the change you need UPDATE ... SET col = col - ....
• If a GIN index covers the JSONB column, a bulk UPDATE rebuilds it — on large tables that is far from free.
• In a #- path a number is treated as an array index; make sure the node really is an array, or the element will not be found.

Deletion often travels with jsonb_set: one expression throws out the unwanted part, the other writes a fresh value.

-- 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;

In MySQL the equivalent is JSON_REMOVE(doc, '$.path'), which accepts one or many paths. In ClickHouse JSON columns are usually immutable, so a delete is expressed by INSERTing a rebuilt value rather than via a pinpoint operator.