JSONB existence (?) operator in PostgreSQL - Step-by-Step Execution

Concept Flow - JSONB existence (?) operator

Start with JSONB column

↓

Apply ? operator with key

↓

Check if key exists in JSONB

↓

Return TRUE

↓

Return FALSE

The ? operator checks if a specified key exists in a JSONB column and returns true or false.

Execution Sample

PostgreSQL

SELECT data ? 'name' AS has_name FROM users;

This query checks if the key 'name' exists in the JSONB column 'data' for each row in 'users'.

Execution Table

Row	data (JSONB)	Key Checked	Condition (key exists?)	Result (TRUE/FALSE)
1	{"name": "Alice", "age": 30}	name	Exists	TRUE
2	{"age": 25, "city": "NY"}	name	Does not exist	FALSE
3	{"name": "Bob", "city": "LA"}	name	Exists	TRUE
4	{"city": "Chicago"}	name	Does not exist	FALSE

💡 All rows checked; query returns TRUE if key exists, FALSE otherwise.

Variable Tracker

Variable	Start	Row 1	Row 2	Row 3	Row 4
data	N/A	{"name": "Alice", "age": 30}	{"age": 25, "city": "NY"}	{"name": "Bob", "city": "LA"}	{"city": "Chicago"}
key	N/A	name	name	name	name
result	N/A	TRUE	FALSE	TRUE	FALSE

Key Moments - 2 Insights

Why does the query return FALSE for row 2 even though the JSONB has other keys?

Because the ? operator checks only for the specific key 'name'. Row 2's JSONB does not have 'name', so it returns FALSE as shown in execution_table row 2.

Can the ? operator check for nested keys inside JSON objects?

No, the ? operator only checks for top-level keys in the JSONB column. Nested keys require different functions like jsonb_path_exists.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result for row 3?

A FALSE

B TRUE

C NULL

D Error

💡 Hint

Check the 'Result' column for row 3 in the execution_table.

At which row does the key 'name' NOT exist according to the execution_table?

A Row 1

B Row 2

C Row 3

D Row 4

💡 Hint

Look at the 'Condition' column for rows where it says 'Does not exist'.

If the key checked was changed to 'city', what would be the result for row 1?

A TRUE

B NULL

C FALSE

D Error

💡 Hint

Refer to the 'data' JSONB in row 1 and check if 'city' key exists.

Concept Snapshot

JSONB existence (?) operator:
- Syntax: jsonb_column ? 'key'
- Returns TRUE if 'key' exists at top-level in JSONB
- Returns FALSE if 'key' is missing
- Only checks top-level keys, not nested
- Useful for quick key presence checks in JSONB columns

Full Transcript

The JSONB existence operator (?) in PostgreSQL checks if a specific key exists in a JSONB column. For each row, it looks at the JSONB data and returns TRUE if the key is present at the top level, otherwise FALSE. This operator does not check nested keys inside objects. For example, if the JSONB column has {"name": "Alice"}, checking for 'name' returns TRUE. If the key is missing, it returns FALSE. This is useful to filter or find rows containing certain keys in JSONB data.