sql · level 3 · unit 7 · lesson 2
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
and pull values out of text.
Apps often store events as JSON, text like {"user": "Ava", "basket": {"total": 9}}. Curly brackets hold named values and square brackets hold lists. SQLite can read inside it.
json_extract(body, '$.user') follows a path: $ is the whole thing, .user is a name inside it, and $.basket.total is a name inside a name. body ->> '$.user' does the same, shorter. A missing path gives NULL.
Broken JSON makes both stop with malformed JSON. Real data always has some, so filter with WHERE json_valid(body) first.
SELECT id,
body ->> '$.user' AS user,
body ->> '$.action' AS action,
body ->> '$.basket.total' AS total
FROM events
WHERE json_valid(body)
ORDER BY id;Event 5 was cut off halfway, so json_valid gives 0 and the row is skipped.
guess first
Events per device, broken row and all. What shows?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT body ->> '$.device' AS device, COUNT(*) AS events
FROM events
WHERE json_valid(body)
GROUP BY device
ORDER BY events DESC, device;your turn
Your turn. Who spent the most?
Fill the blanks so the query adds up the basket total of every valid buy event, for each user.
result
Your answer appears here as a table.
fix it
One broken event stops the whole query. Fix it!
result
Your answer appears here as a table.
- Read the error. Could one of the events not be proper JSON?
- Event 5 was cut off halfway. Keep only valid JSON before reading inside it.
- Start the condition with json_valid(body) AND, before the json_extract test, then press run.
nailed it
you read data from inside json. :D
body ->> '$.user'pulls one value out of JSON text.- Paths start at
$and step inside with dots. - Filter with
json_validbefore reading real-world JSON.
new words in your collection
- JSON
- json_extract
- ->>
This lesson isn’t saved. A free account remembers every lesson you finish.