You have a table with a JSONB column indexed by GIN. You want to find rows where the JSONB contains either {"type": "admin"} or {"type": "user"}. Which query uses the GIN index efficiently?
hard📝 Application Q9 of 15
PostgreSQL - JSON and JSONB
You have a table with a JSONB column indexed by GIN. You want to find rows where the JSONB contains either {"type": "admin"} or {"type": "user"}. Which query uses the GIN index efficiently?
ASELECT * FROM table WHERE jsonb_column @> '{"type": "admin"}' OR jsonb_column @> '{"type": "user"}';
BSELECT * FROM table WHERE jsonb_column @> '{"type": "admin", "type": "user"}';
CSELECT * FROM table WHERE jsonb_column->>'type' = 'admin' OR jsonb_column->>'type' = 'user';
DSELECT * FROM table WHERE jsonb_column ?| array['admin', 'user'];
Step-by-Step Solution
Solution:
Step 1: Understand GIN index usage with @>
Queries using @> operator can use GIN indexes efficiently for containment checks.
Step 2: Analyze each option
SELECT * FROM table WHERE jsonb_column @> '{"type": "admin"}' OR jsonb_column @> '{"type": "user"}'; uses OR with two @> conditions, both can use the GIN index. SELECT * FROM table WHERE jsonb_column @> '{"type": "admin", "type": "user"}'; is invalid JSON. SELECT * FROM table WHERE jsonb_column->>'type' = 'admin' OR jsonb_column->>'type' = 'user'; uses text extraction which may not use GIN. SELECT * FROM table WHERE jsonb_column ?| array['admin', 'user']; uses ?| operator which checks top-level keys, not values.
Final Answer:
SELECT * FROM table WHERE jsonb_column @> '{"type": "admin"}' OR jsonb_column @> '{"type": "user"}'; -> Option A
Quick Check:
Use OR with @> for efficient GIN index queries on JSONB [OK]
Quick Trick:Use OR with @> operator to leverage GIN index for multiple values [OK]
Common Mistakes:
Using invalid JSON in query
Using text extraction that bypasses GIN index
Using ?| operator incorrectly for values
Master "JSON and JSONB" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
