JSONB Key-Existence Operators in PostgreSQL: ?, ?| and ?&

PostgreSQL's jsonb type ships three operators for checking that a key is present: ? (has key), ?| (has any of these keys) and ?& (has all of these keys). They answer "does the key exist", not "what is its value", and with the right index they are extremely fast.

The three operators

Suppose users has a prefs jsonb column holding settings and feature flags. The basic checks look like this:

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

Key points:

• ? matches a string against object keys or against elements of a string array.
• ?| and ?& take a text[] — a SQL array of strings, not a JSON array.
• For jsonb, these operators inspect only the top level. They do not see nested keys.

Top-level only

This is the main trap. The ? operator does not descend into the object:

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

To probe a nested key, drill down first with ->:

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

Remember that -> returns jsonb, and if notify is missing the result is NULL, not an error. Then NULL ? 'email' is also NULL, so the row simply drops out of the result set.

Checking a feature flag

A common task is "is this flag enabled for the user". If flags are stored as object keys, the check is trivial:

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

Be careful to distinguish "key exists" from "value is truthy". ? answers only the first:

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

If flags live in a string array, the same ? works as "array contains element":

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

Speeding it up with a GIN index

The ?, ?| and ?& operators are GIN-indexable, so existence checks scale. With the default operator class (jsonb_ops), one index covers both these operators and @>:

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

Caveats:

• GIN indexes the top-level keys. An expression like (prefs -> 'flags') ? 'beta_ui' is not accelerated by a plain GIN on prefs; you would need an expression index on (prefs -> 'flags').
• The jsonb_path_ops class is smaller and faster for @>, but it does not support ?, ?| or ?&. If you need the existence operators, stay on jsonb_ops.

Gotcha: ? in parameterized queries

The nastiest problem is the clash between ? and parameter placeholders. Many drivers (JDBC, ODBC, several libraries) read ? as a bind marker, so the query breaks before it ever reaches the database.

Ways around it:

• In PostgreSQL use numbered placeholders like $1, so the literal ? operator is never mistaken for a parameter.
• Replace the operator with a function: jsonb_exists(prefs, 'email') for ?, jsonb_exists_any(...) for ?|, and jsonb_exists_all(...) for ?&. The functions contain no ? and are safe with any 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']);

About other engines: MySQL has no equivalent operator — you test for a path with JSON_CONTAINS_PATH(prefs, 'one', '$.email'). ClickHouse uses functions such as JSONHas(prefs, 'email'). The ?, ?| and ?& operators are a PostgreSQL idiom, and combined with a GIN index they give you a very cheap key-existence check.