[Solved] You need to retrieve all rows from the events table where the JSONB column metadata contains at least one... Which of the following queries correctly uses JSONB existence operators to achieve this? | PostgreSQL

PostgreSQL - JSON and JSONB

You need to retrieve all rows from the events table where the JSONB column metadata contains at least one of the keys 'user' or 'admin'. Which of the following queries correctly uses JSONB existence operators to achieve this?

ASELECT * FROM events WHERE metadata ? 'user' AND metadata ? 'admin';

BSELECT * FROM events WHERE metadata ? 'user' OR metadata ? 'admin';

CSELECT * FROM events WHERE metadata ?& ARRAY['user', 'admin'];

DSELECT * FROM events WHERE metadata ?| ARRAY['user', 'admin'];

Step-by-Step Solution

Solution:

Step 1: Understand the operators
? checks a single key, ?| checks if any key in an array exists, ?& checks if all keys exist.
Step 2: Identify the requirement
We want rows where either 'user' or 'admin' key exists (any key).
Step 3: Match the correct operator
?| matches any key in the array, so SELECT * FROM events WHERE metadata ?| ARRAY['user', 'admin']; is correct.
Final Answer:
SELECT * FROM events WHERE metadata ?| ARRAY['user', 'admin']; -> Option D
Quick Check:
Use ?| for any key existence in array [OK]

Quick Trick: Use ?| to check if any key exists in JSONB [OK]

Common Mistakes:

Using ?& which requires all keys to exist
Using multiple OR conditions instead of ?|
Confusing ? with ?| and ?& operators

Master "JSON and JSONB" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Which of the following queries correctly uses JSONB existence operators to achieve this?

Step 1: Understand the operators

Step 2: Identify the requirement

Step 3: Match the correct operator

Final Answer:

Quick Check:

Want More Practice?