Pick the Primary Address from a Nested Array

Write a query to return each purchase in app_events with event_id, city and issue, using the payload’s nested addresses array:

none: Exactly one address has Boolean primary set to true; return its city.

no_addresses: The array is absent, null or empty; return a NULL city.

no_primary: Addresses exist but none is marked primary; return a NULL city.

many_primary: More than one address is marked primary; return a NULL city.

An absent primary key is not true. Return every purchase exactly once; do not choose a city when several addresses are primary.

Ordering: Sort by event_id in ascending order.

Returnevent_idcityissue

Columns

app_events4 columns

columntype

event_idVARCHAR

user_idVARCHAR

event_typeVARCHAR

payloadVARCHAR

Sample data & expected output

7 rows · 4 columns

event_iduser_idevent_typepayload

ev_1u_1purchase{"addresses":[{"city":"Pune","primary":true},{"city":"Delhi","primary":false}]}

ev_2u_2purchase{"addresses":[]}

ev_3u_3purchase{"plan":"pro"}

ev_4u_1purchase{"addresses":[{"city":"Goa"},{"city":"Agra","primary":true},{"city":"Mysore","primary":true}]}

ev_5u_4purchase{"addresses":[{"city":"Kochi","primary":false},{"city":"Surat"}]}

ev_6u_2purchase{"addresses":[{"city":"Solo","primary":true}]}

ev_7u_5signup{"addresses":[{"city":"Nope","primary":true}]}

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise working with an array of objects, which is where most real JSON lives, and reporting honestly when the data does not answer the question. Each purchase may carry several addresses, and you return the primary one’s city, or nothing at all plus a reason. The skill is counting two different things over the same expansion and keeping the events whose arrays are empty.

Three mistakes each produce a confident wrong answer. Expanding with a plain join drops the purchases that have no addresses, so the two events most in need of attention never reach the report. Using count(*) instead of count(obj) keeps them but mislabels them: the left join supplies one row with a NULL object, which counts as an address, and no_addresses becomes no_primary – a reason that points at the wrong fix. And treating any primary key as primary, rather than checking it is true, pulls in the address that explicitly says false; ev_1 then looks like it has two primaries and ev_5 looks like it has one, which is both directions of wrong from a single lazy test. The many_primary case is the one worth dwelling on: max() will happily return a city for ev_4, and it is only the outer guard that stops the report asserting a shipping address that the data does not support.

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

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows