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.

Practice

1.

What does the PostgreSQL JSONB existence operator ? check for?

easy

A. If a JSONB column contains any null values

B. If a JSONB column is empty

C. If a specified key or string exists at the top level in a JSONB column

D. If a JSONB column contains nested arrays

Solution

1. Step 1: Understand the operator's purpose

   The ? operator checks for the presence of a key or string in JSONB data.

2. Step 2: Identify the scope of the check

   It only checks the top-level keys or strings, not nested elements.

3. Final Answer:

   If a specified key or string exists at the top level in a JSONB column -> Option C

4. Quick Check:

   JSONB existence operator checks top-level keys [OK]

Hint: Remember: '?' checks top-level keys only in JSONB [OK]

Common Mistakes:

• Thinking it checks nested keys
• Confusing it with null or empty checks
• Assuming it works on JSON type (not JSONB)
• Believing it checks values instead of keys

2.

Which of the following is the correct syntax to check if the key 'name' exists in a JSONB column data?

SELECT * FROM users WHERE data ? 'name';

easy

A. SELECT * FROM users WHERE data ? name;

B. SELECT * FROM users WHERE data ? 'name';

C. SELECT * FROM users WHERE data ? "name";

D. SELECT * FROM users WHERE data ? :name;

Solution

1. Step 1: Check operator usage

   The ? operator requires the key as a string literal in single quotes.

2. Step 2: Validate syntax correctness

   SELECT * FROM users WHERE data ? 'name'; uses single quotes correctly; others either miss quotes or use invalid syntax.

3. Final Answer:

   SELECT * FROM users WHERE data ? 'name'; -> Option B

4. Quick Check:

   Key must be a single-quoted string [OK]

Hint: Use single quotes around key with '?' operator [OK]

Common Mistakes:

• Omitting quotes around the key
• Using double quotes instead of single quotes
• Using parameter syntax (:name) incorrectly
• Confusing key with column name without quotes

3.

Given the table products with a JSONB column attributes containing {"color": "red", "size": "M"}, what will this query return?

SELECT id FROM products WHERE attributes ? 'color';

medium

A. Syntax error due to incorrect operator usage

B. All product IDs where the value of 'color' is 'red'

C. All product IDs where the 'color' key does not exist

D. All product IDs where the 'color' key exists in attributes

Solution

1. Step 1: Understand the query condition

   The condition attributes ? 'color' checks if the key 'color' exists in the JSONB column.

2. Step 2: Determine the query result

   It returns all rows where 'color' is a key, regardless of its value.

3. Final Answer:

   All product IDs where the 'color' key exists in attributes -> Option D

4. Quick Check:

   Operator '?' checks key existence, not value [OK]

Hint: Remember '?' checks keys, not values [OK]

Common Mistakes:

• Thinking it filters by key value
• Assuming it returns rows without the key
• Confusing '?' with containment operator
• Expecting syntax error incorrectly

4.

Identify the error in this query that checks if the key "status" exists in the JSONB column info:

SELECT * FROM orders WHERE info ? status;

medium

A. Missing single quotes around the key 'status'

B. Using double quotes instead of single quotes

C. The operator '?' cannot be used with JSONB columns

D. The column name 'info' is invalid

Solution

1. Step 1: Check key syntax in operator

   The key must be a string literal in single quotes when using the '?' operator.

2. Step 2: Identify the error in the query

   The query uses status without quotes, causing a syntax error or wrong interpretation.

3. Final Answer:

   Missing single quotes around the key 'status' -> Option A

4. Quick Check:

   Keys must be single-quoted strings [OK]

Hint: Always quote keys with single quotes in '?' operator [OK]

Common Mistakes:

• Using unquoted keys
• Using double quotes instead of single quotes
• Thinking '?' works on non-JSONB columns
• Assuming column name is the problem

5.

You want to find all rows in the events table where the JSONB column details contains either the key 'location' or the key 'venue'. Which query correctly uses the JSONB existence operator to achieve this?

hard

A. SELECT * FROM events WHERE details ?| array['location', 'venue'];

B. SELECT * FROM events WHERE details ?& array['location', 'venue'];

C. SELECT * FROM events WHERE details ? array['location', 'venue'];

D. SELECT * FROM events WHERE details ? 'location' AND details ? 'venue';

Solution

1. Step 1: Understand the requirement

   We want rows where either 'location' OR 'venue' keys exist in the JSONB column.

2. Step 2: Choose the correct operator

   The ?| operator checks if any key in the array exists in the JSONB data.

3. Step 3: Compare options

   SELECT * FROM events WHERE details ?| array['location', 'venue']; uses ?| array['location', 'venue'], which matches the requirement.

4. Final Answer:

   SELECT * FROM events WHERE details ?| array['location', 'venue']; -> Option A

5. Quick Check:

   Use '?|' for any key in array [OK]

Hint: Use '?|' with array for checking multiple keys [OK]

Common Mistakes:

• Using OR with multiple '?' instead of '?|'
• Using '?&' which requires all keys
• Using AND instead of OR logic
• Confusing single '?' with multiple keys