You have a table events with a JSONB column data storing event details. You want to find all events where the nested JSON key location.city is 'Paris'. Which query correctly achieves this?
hard📝 Application Q15 of 15
PostgreSQL - JSON and JSONB
You have a table events with a JSONB column data storing event details. You want to find all events where the nested JSON key location.city is 'Paris'. Which query correctly achieves this?
ASELECT * FROM events WHERE data->'location'->>'city' = 'Paris';
BSELECT * FROM events WHERE data->>'location.city' = 'Paris';
CSELECT * FROM events WHERE data->'location.city' = 'Paris';
DSELECT * FROM events WHERE data->'location'->'city' = 'Paris';
Step-by-Step Solution
Solution:
Step 1: Access nested JSON keys correctly
To get nested keys, chain -> for JSON objects and ->> for text extraction. Here, data->'location' gets the location object, then ->>'city' extracts city as text.
Step 2: Check each option
data->>'location.city' treats 'location.city' as a single key. data->'location.city' does the same with ->. data->'location'->'city' returns JSON for 'city', causing type mismatch when compared to 'Paris'. Only data->'location'->>'city' extracts text correctly.
Final Answer:
SELECT * FROM events WHERE data->'location'->>'city' = 'Paris'; -> Option A
Quick Check:
Nested keys need chained -> and ->> [OK]
Quick Trick:Chain -> for objects, ->> for text extraction in JSON [OK]
Common Mistakes:
Using dot notation inside ->> operator
Comparing JSON object directly to string
Not chaining operators for nested keys
Master "JSON and JSONB" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
