Operadores de existencia de claves JSONB en PostgreSQL: ?, ?| y ?&

El tipo jsonb de PostgreSQL incluye tres operadores para comprobar que una clave esta presente: ? (tiene la clave), ?| (tiene alguna de estas claves) y ?& (tiene todas estas claves). Responden a "existe la clave", no a "cual es su valor", y con el indice adecuado son extremadamente rapidos.

Los tres operadores

Supongamos que users tiene una columna prefs jsonb con ajustes y feature flags. Las comprobaciones basicas son asi:

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

Puntos clave:

• ? compara una cadena con las claves del objeto o con los elementos de un array de cadenas.
• ?| y ?& reciben un text[]: un array SQL de cadenas, no un array JSON.
• En jsonb, estos operadores solo inspeccionan el nivel superior. No ven claves anidadas.

Solo el nivel superior

Esta es la trampa principal. El operador ? no desciende dentro del 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 examinar una clave anidada, baja primero con ->:

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

Recuerda que -> devuelve jsonb, y si falta notify el resultado es NULL, no un error. Entonces NULL ? 'email' tambien es NULL, asi que la fila simplemente queda fuera del resultado.

Comprobar un feature flag

Una tarea habitual es "esta activada esta bandera para el usuario". Si las banderas se guardan como claves del objeto, la comprobacion es trivial:

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

Ten cuidado de distinguir "la clave existe" de "el valor es verdadero". ? solo responde a lo primero:

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

Si las banderas estan en un array de cadenas, el mismo ? funciona como "el array contiene el elemento":

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

Acelerarlo con un indice GIN

Los operadores ?, ?| y ?& son indexables con GIN, asi que las comprobaciones de existencia escalan. Con la clase de operadores por defecto (jsonb_ops), un mismo indice cubre estos operadores y @>:

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

Matices:

• GIN indexa las claves de nivel superior. Una expresion como (prefs -> 'flags') ? 'beta_ui' no se acelera con un GIN simple sobre prefs; necesitarias un indice de expresion sobre (prefs -> 'flags').
• La clase jsonb_path_ops es mas pequena y rapida para @>, pero no soporta ?, ?| ni ?&. Si necesitas los operadores de existencia, quedate con jsonb_ops.

Trampa: ? en consultas parametrizadas

El problema mas molesto es el choque entre ? y los marcadores de parametros. Muchos drivers (JDBC, ODBC, varias librerias) leen ? como marcador de bind, asi que la consulta se rompe antes de llegar a la base de datos.

Como evitarlo:

• En PostgreSQL usa marcadores numerados como $1, asi el operador literal ? nunca se confunde con un parametro.
• Reemplaza el operador por una funcion: jsonb_exists(prefs, 'email') para ?, jsonb_exists_any(...) para ?| y jsonb_exists_all(...) para ?&. Las funciones no contienen ? y son seguras con cualquier 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 otros motores: MySQL no tiene un operador equivalente; compruebas un path con JSON_CONTAINS_PATH(prefs, 'one', '$.email'). ClickHouse usa funciones como JSONHas(prefs, 'email'). Los operadores ?, ?| y ?& son un idioma propio de PostgreSQL y, junto a un indice GIN, te dan una comprobacion de existencia de claves muy economica.