Write a query to return every app_events.event_id with status and detail, applying the first matching rule:
01unparseable: payload is NULL or invalid JSON; detail is NULL.
02not_an_object: Valid JSON whose top-level type is not an object; detail is that JSON type.
03missing_fields: An object lacks plan, amount or both; detail lists the missing key names in ascending comma-separated order, such as amount,plan.
04bad_amount: Both keys exist but amount is not readable as a number; detail is the extracted amount as text.
05ok: No earlier rule applies; detail is NULL.
A JSON-null value still means its key is present. Therefore amount:null reaches bad_amount, not missing_fields. Invalid input must not make the query fail.
Ordering: Sort by event_id in ascending order.
Returnevent_idstatusdetail
About this problem
You practise writing a query that survives its own input. The payload column holds a truncated document, a NULL, an array, a bare string, objects missing their fields, and an amount that is not a number, and you return one row per event saying which of those it is. The skill is ordering the checks so that each one only runs on data the previous one has already made safe.
The thing that makes this hard rather than tedious is that an extraction on malformed JSON does not return NULL, it raises an error – so the natural query, which tests the payload’s contents directly, does not produce a report with some rows marked bad. It produces nothing, for every row, because of one row. That is the real-world failure this problem is modelled on: a pipeline that ran fine for months and then stopped completely when a single producer sent a single truncated line. After that, the remaining judgement is about distinctions that extractions cannot make. A valid array is not a missing-fields object, and a JSON null amount is not an absent amount – in both cases the lazier test gives an answer that looks reasonable in the output and would send someone to debug the wrong producer.
Read the lesson: JSON & Semi-structured (P14) →