Apagar de JSONB no PostgreSQL: os operadores - e #-

Um documento JSONB quase nunca esta finalizado: chaves soltas, flags temporarias e campos com segredos precisam sumir antes que a linha chegue ao mundo externo. O PostgreSQL traz dois operadores de remocao — - para o nivel superior e #- para um caminho aninhado. Ambos devolvem um documento novo e nunca alteram o original.

O operador -: uma chave ou um indice de array

O operando esquerdo e jsonb; o direito diz o que remover. Uma string apaga uma chave de nivel superior; um inteiro apaga um elemento do array pelo indice, e um indice negativo conta a partir do fim.

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

Suponha que users tenha uma coluna profile jsonb com configuracoes. Vamos remover uma chave temporaria em todas as linhas:

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

Apagar varias chaves de uma vez

Quando o operando direito e um array text[], - apaga todas as chaves de nivel superior listadas em uma unica passada. Isso e pratico para higienizar um documento antes de serializa-lo em uma resposta de API.

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

Atencao: a forma com array so afeta o nivel superior. Ela nao alcanca os campos aninhados — para esses voce precisa do #-.

O operador #-: um valor em um caminho aninhado

O #- recebe a direita um caminho como text[] e apaga o que estiver no fim desse caminho. Os elementos do caminho sao nomes de chave e indices de array, na ordem do aninhamento.

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

Na pratica o #- costuma limpar dados de log em orders.meta jsonb antes de uma exportacao:

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

Pegadinhas e combinacao com jsonb_set

• Uma chave ou caminho inexistente nao e erro: o operador apenas devolve o documento sem mudancas. Filtre com ? ou #> para nao disparar UPDATEs vazios.
• - e #- sempre devolvem um novo jsonb. Para persistir a mudanca voce precisa de UPDATE ... SET col = col - ....
• Se um indice GIN cobre a coluna JSONB, um UPDATE em massa o reconstroi — em tabelas grandes isso esta longe de ser barato.
• Em um caminho de #- um numero e tratado como indice de array; garanta que o no seja de fato um array, ou o elemento nao sera encontrado.

A remocao costuma andar junto com jsonb_set: uma expressao joga fora a parte indesejada, a outra grava um valor novo.

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

No MySQL o equivalente e JSON_REMOVE(doc, '$.path'), que aceita um ou varios caminhos. No ClickHouse as colunas JSON costumam ser imutaveis, entao uma remocao e expressa com um INSERT do valor reconstruido, e nao por um operador pontual.