Expand the Tags into Rows

Write a query to return one row per tag entry for each purchase event in app_events, with event_id and tag as plain text.

Keep duplicate tag entries as duplicate rows. A purchase with an empty tags array or no tags key must still appear once with tag set to NULL.

Ordering: Sort by event_id, then tag in ascending order.

Returnevent_idtag

Columns

app_events4 columns

columntype

event_idVARCHAR

user_idVARCHAR

event_typeVARCHAR

payloadVARCHAR

Sample data & expected output

5 rows · 4 columns

event_iduser_idevent_typepayload

ev_2u_2purchase{"plan":"team","tags":[]}

ev_3u_3purchase{"plan":"pro"}

ev_4u_1signup{"source":"ads","tags":["ignored"]}

ev_5u_4purchase{"plan":"basic","tags":["vip","gift","vip"]}

ev_6u_2purchase{"plan":"pro","tags":["solo"]}

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise turning a JSON array into rows without losing the events that have nothing in the array. Each purchase may carry tags, and you return one row per tag, keeping a single NULL row for any purchase whose array is empty or absent. The skill is noticing that expanding an array is a join, and that an empty array on the right-hand side of a join behaves exactly like no match.

The straightforward query – unnest in the SELECT list – drops two of the four purchases, and nothing warns you. This is the single most expensive habit in semi-structured SQL, because it does not fail, it just quietly narrows the population: a report of “purchases by tag” built this way silently excludes every untagged purchase, and the totals look plausible. The remaining judgement call is the duplicate tag on ev_5: DISTINCT would make the output prettier and would be answering a question nobody asked.

Read the lesson: JSON & Semi-structured (P14) →

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows