Write a query to return each purchase event in app_events with event_id and amount_type, based on the payload’s top-level amount:
02text: A JSON string, including a string that looks numeric.
03boolean: JSON true or false.
04null: A present key containing JSON null.
05missing: No amount key.
06other: Any other value, including arrays and objects.
Keep null and missing separate. Include only purchase events.
Ordering: Sort by event_id in ascending order.
Returnevent_idamount_type
About this problem
You practise finding out what is really inside a JSON field instead of assuming. Across the purchase events, amount turns up as a whole number, a quoted string, a boolean, a JSON null and a missing key, and you report each one with a plain word so a reader can see how consistent the feed is. The skill is handling the two cases that are easy to confuse and the two that are easy to merge.
The first confusion is between a JSON null and a missing key. json_type tells them apart, but it does so in a way that is easy to miss: a JSON null gives back the four-character string 'NULL', while a missing key gives back a real SQL NULL. Both render as NULL on screen, so a query written with coalesce(json_type(...), 'missing') labels ev_5 as missing and nobody notices. The second trap is the shorter-looking CASE json_type(...) WHEN NULL THEN 'missing' form, which never fires at all, because nothing equals NULL – the missing rows quietly fall through to the catch-all branch instead. The last thing worth noticing is ev_2, where "750.00" is text. It will cast to a number perfectly well, which is exactly why a type report is useful: the value works today and will stop working the first time someone sends "n/a" in the same column.
Read the lesson: JSON & Semi-structured (P14) →