How to Use ? Operator for jsonb in PostgreSQL

In PostgreSQL, the ? operator checks if a jsonb column contains a specific key. You use it in a WHERE clause like jsonb_column ? 'key' to filter rows where the key exists.

Syntax

The ? operator is used with a jsonb column and a key string. It returns true if the key exists in the JSON object, otherwise false.

• jsonb_column ? 'key': Checks if the key exists in the JSON object.

sql

jsonb_column ? 'key'

Example

This example shows how to find rows where the data jsonb column contains the key name.

sql

CREATE TABLE users (id serial PRIMARY KEY, data jsonb);

INSERT INTO users (data) VALUES
  ('{"name": "Alice", "age": 30}'),
  ('{"age": 25}'),
  ('{"name": "Bob"}');

SELECT id, data
FROM users
WHERE data ? 'name';

Output

 id |             data             
----+------------------------------
  1 | {"name": "Alice", "age": 30}
  3 | {"name": "Bob"}

Common Pitfalls

Common mistakes include:

• Using the ? operator on json type instead of jsonb. It only works with jsonb.
• Checking for a value instead of a key. The ? operator only checks keys, not values.
• Using double quotes around the key inside the operator, which is incorrect. Use single quotes.

sql

/* Wrong: Using json type */
CREATE TABLE test_json (data json);
-- This will cause an error:
-- SELECT data ? 'key' FROM test_json;

/* Wrong: Checking value instead of key */
SELECT data ? 'Alice' FROM users; -- returns false

/* Correct usage */
SELECT data ? 'name' FROM users;

Quick Reference

Operator	Description	Example
?	Checks if jsonb contains a key	data ? 'name'
?|	Checks if jsonb contains any of the keys in an array	data ?| array['name', 'age']
?&	Checks if jsonb contains all keys in an array	data ?& array['name', 'age']

Key Takeaways

The ? operator checks if a jsonb column contains a specific key.

It only works with jsonb type, not json.

Use single quotes around the key string inside the operator.

The operator returns true if the key exists, false otherwise.

For multiple keys, use ?| or ?& operators.