The First and Last Tag in Each Array

Write a query to return each purchase event in app_events with its event_id, first_tag and last_tag, as plain text from the payload’s tags array.

Use array order, not alphabetical order. A one-entry array returns that entry in both columns. An empty or absent array returns NULL in both.

Ordering: Sort by event_id in ascending order.

Returnevent_idfirst_taglast_tag

Columns

app_events4 columns

columntype

event_idVARCHAR

user_idVARCHAR

event_typeVARCHAR

payloadVARCHAR

Sample data & expected output

6 rows · 4 columns

event_iduser_idevent_typepayload

ev_1u_1purchase{"tags":["new","promo","vip"]}

ev_2u_2purchase{"tags":["solo"]}

ev_3u_3purchase{"tags":[]}

ev_4u_1purchase{"plan":"pro"}

ev_5u_4signup{"tags":["a","b"]}

ev_6u_2purchase{"tags":["first","second"]}

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise reading individual elements out of a JSON array by position, which is what you do whenever the order in an array carries meaning. For each purchase you return the first and last tag, with NULL where the array is empty or absent. The skill is keeping two different counting conventions straight in one query.

Almost every wrong answer here is an off-by-one. Inside a JSONPath the first element is [0]; after CAST(... AS VARCHAR[]) the first element is [1] and asking for [0] returns NULL for every row. The two conventions sit a few characters apart in the same query, and the failure mode differs: ask for $.tags[1] and you get the second tag, which is a plausible-looking value and so survives review, whereas [0] on a DuckDB list gives NULL everywhere and at least looks broken. The last element has the same shape of problem – the hand-rolled version using json_array_length works only if you remember to subtract one, which is exactly the kind of thing that is right on the day you write it and wrong after someone edits it.

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

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows