sql · level 3 · unit 7 · lesson 3
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
turns a JSON list into rows, and back again.
Each recipe stores its tags as a JSON list, such as ["spicy", "vegan"]. To count tags, each tag needs its own row. json_each does that. Write it after a comma in FROM, as json_each(r.tags) AS t, and every item becomes a row, in t.value.
The other way builds JSON for an app. gathers many rows into one sorted list. builds named values.
Together, you can unpack, count and repack data in one query.
SELECT t.value AS tag, COUNT(*) AS recipes
FROM recipes AS r, json_each(r.tags) AS t
GROUP BY t.value
ORDER BY recipes DESC, tag;json_each runs once for every recipe row, so each recipe's tags line up with its name.
guess first
Vegan recipes, gathered up. What comes back?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT json_group_array(r.name ORDER BY r.name) AS vegan
FROM recipes AS r, json_each(r.tags) AS t
WHERE t.value = 'vegan';your turn
Your turn. Build a card for every tag.
Fill the blanks: unpack each recipe's tags into rows, then gather the recipe names for each tag into a JSON list.
result
Your answer appears here as a table.
fix it
The tag count cannot find its column. Fix it!
result
Your answer appears here as a table.
- Read the error. Which column can the database not find?
json_eachputs every item in a column calledvalue, whatever the items are.- Change both t.tag to t.value, then press run.
nailed it
you can unpack and repack json. :D
json_eachturns a JSON list into one row per item.- Each item sits in a column called
value. json_group_arrayandjson_objectbuild JSON from rows.
new words in your collection
- json_each
- json_group_array
- json_object
This lesson isn’t saved. A free account remembers every lesson you finish.