JSONB existence (?) operator in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Check if a JSONB key exists in a column

Given a table products with a JSONB column attributes, what is the output of this query?

SELECT id FROM products WHERE attributes ? 'color';

Assuming the table has these rows:

id | attributes
---+----------------------------
1  | {"color": "red", "size": "M"}
2  | {"weight": 10}
3  | {"color": "blue"}
4  | {"size": "L"}

A [1, 2, 3]

B [1, 3]

C [2, 4]

D [3, 4]

💡 Hint

The ? operator checks if the JSONB column contains the specified key at the top level.

The query filters rows where the JSONB column 'attributes' contains the key 'color'. Rows 1 and 3 have 'color' as a key, so their ids are returned.

🧠 Conceptual

intermediate

Understanding the JSONB existence (?) operator behavior

Which statement correctly describes what the JSONB existence operator ? does in PostgreSQL?

A It checks if a JSONB column contains a specific key at the top level.

B It checks if a JSONB column contains a specific value anywhere inside the JSON.

C It checks if a JSONB column contains a specific key or nested key at any depth.

D It checks if a JSONB column contains a specific key and value pair.

💡 Hint

Think about whether the operator looks inside nested objects or only top-level keys.

The ? operator tests for the presence of a top-level key in a JSONB column. It does not search nested keys or values.

📝 Syntax

advanced

Identify the correct syntax for using the JSONB existence operator

Which of the following SQL queries correctly uses the JSONB existence operator to find rows where the JSONB column data contains the key active?

A SELECT * FROM users WHERE data ? 'active';

B SELECT * FROM users WHERE data ? active;

C SELECT * FROM users WHERE data ? "active";

D SELECT * FROM users WHERE data ? :active;

💡 Hint

Remember that the key must be a string literal in single quotes.

The ? operator requires the key to be a string literal enclosed in single quotes. Option A is correct syntax.

❓ optimization

advanced

Optimizing queries using the JSONB existence operator

You want to speed up queries that use WHERE attributes ? 'status' on a large table. Which index type is best to optimize this query?

A A hash index on the JSONB column

B A B-tree index on the JSONB column

C A GIN index on the JSONB column

D A full-text index on the JSONB column

💡 Hint

Think about which index type supports key existence queries on JSONB.

GIN indexes support fast existence checks on JSONB keys using the ? operator. B-tree and hash indexes do not support this efficiently.

🔧 Debug

expert

Diagnose the error in this JSONB existence operator query

A developer runs this query:

SELECT * FROM orders WHERE details ? status;

and gets an error. What is the cause?

A The query is missing a FROM clause.

B The JSONB column 'details' does not exist.

C The ? operator cannot be used in WHERE clauses.

D The key 'status' is not quoted as a string literal.

💡 Hint

Check how the key is written in the query.

The ? operator requires the key to be a string literal in single quotes. Without quotes, PostgreSQL treats status as a column or variable, causing an error.