Operadores de existencia de chaves JSONB no PostgreSQL: ?, ?| e ?&

O tipo jsonb do PostgreSQL traz tres operadores para verificar se uma chave esta presente: ? (tem a chave), ?| (tem alguma destas chaves) e ?& (tem todas estas chaves). Eles respondem "a chave existe", nao "qual e o valor dela", e com o indice certo sao extremamente rapidos.

Os tres operadores

Suponha que users tenha uma coluna prefs jsonb com configuracoes e feature flags. As verificacoes basicas sao assim:

-- has the key "email" at the top level
SELECT id, email
FROM users
WHERE prefs ? 'email';

-- has at least one of these keys
SELECT id
FROM users
WHERE prefs ?| array['newsletter', 'sms'];

-- has all of these keys
SELECT id
FROM users
WHERE prefs ?& array['email', 'phone'];

Pontos importantes:

• ? compara uma string com as chaves do objeto ou com os elementos de um array de strings.
• ?| e ?& recebem um text[]: um array SQL de strings, nao um array JSON.
• Em jsonb, esses operadores inspecionam apenas o nivel superior. Eles nao enxergam chaves aninhadas.

Apenas o nivel superior

Essa e a principal armadilha. O operador ? nao desce para dentro do objeto:

-- prefs = {"notify": {"email": true}}
SELECT prefs ? 'email';   -- false: "email" is nested, not top level
SELECT prefs ? 'notify';  -- true: "notify" is a top-level key

Para inspecionar uma chave aninhada, desca primeiro com ->:

SELECT (prefs -> 'notify') ? 'email';  -- true

Lembre que -> devolve jsonb, e se notify nao existir o resultado e NULL, nao um erro. Entao NULL ? 'email' tambem e NULL, e a linha simplesmente fica de fora do resultado.

Verificar um feature flag

Uma tarefa comum e "esta flag esta ativa para o usuario". Se as flags sao guardadas como chaves do objeto, a verificacao e trivial:

-- prefs.flags = {"beta_ui": true, "dark_mode": true}
SELECT u.id, u.email
FROM users u
WHERE (u.prefs -> 'flags') ? 'beta_ui';

Tenha o cuidado de distinguir "a chave existe" de "o valor e verdadeiro". ? responde apenas o primeiro:

-- key present but value is false -> still matches "?"
-- prefs.flags = {"beta_ui": false}
SELECT (prefs -> 'flags') ? 'beta_ui';        -- true (key exists)
SELECT (prefs -> 'flags' ->> 'beta_ui')::bool; -- false (the value)

Se as flags estiverem em um array de strings, o mesmo ? funciona como "o array contem o elemento":

-- prefs.features = ["beta_ui", "dark_mode"]
SELECT (prefs -> 'features') ? 'beta_ui';  -- true

Acelerando com um indice GIN

Os operadores ?, ?| e ?& sao indexaveis por GIN, entao as verificacoes de existencia escalam. Com a classe de operadores padrao (jsonb_ops), um unico indice cobre esses operadores e tambem o @>:

CREATE INDEX idx_users_prefs ON users USING gin (prefs);

-- uses the GIN index
SELECT id FROM users WHERE prefs ? 'email';
SELECT id FROM users WHERE prefs ?& array['email', 'phone'];

Detalhes:

• O GIN indexa as chaves de nivel superior. Uma expressao como (prefs -> 'flags') ? 'beta_ui' nao e acelerada por um GIN simples em prefs; voce precisaria de um indice de expressao sobre (prefs -> 'flags').
• A classe jsonb_path_ops e menor e mais rapida para @>, mas nao suporta ?, ?| nem ?&. Se voce precisa dos operadores de existencia, fique com jsonb_ops.

Armadilha: ? em consultas parametrizadas

O problema mais chato e o conflito entre ? e os placeholders de parametros. Muitos drivers (JDBC, ODBC, varias bibliotecas) leem ? como marcador de bind, entao a consulta quebra antes de chegar ao banco.

Como contornar:

• No PostgreSQL use placeholders numerados como $1, assim o operador literal ? nunca e confundido com um parametro.
• Substitua o operador por uma funcao: jsonb_exists(prefs, 'email') para ?, jsonb_exists_any(...) para ?| e jsonb_exists_all(...) para ?&. As funcoes nao contem ? e sao seguras com qualquer driver.

-- equivalent to: prefs ? 'email'
SELECT id FROM users WHERE jsonb_exists(prefs, 'email');

-- equivalent to: prefs ?| array['newsletter', 'sms']
SELECT id FROM users WHERE jsonb_exists_any(prefs, array['newsletter', 'sms']);

Sobre outros motores: o MySQL nao tem operador equivalente; voce testa um caminho com JSON_CONTAINS_PATH(prefs, 'one', '$.email'). O ClickHouse usa funcoes como JSONHas(prefs, 'email'). Os operadores ?, ?| e ?& sao um idioma proprio do PostgreSQL e, junto a um indice GIN, oferecem uma verificacao de existencia de chaves muito barata.