Write a query to return every app_events.event_id with is_valid for its text payload: TRUE for valid JSON, FALSE for invalid JSON, and NULL when the payload itself is NULL.
Valid JSON includes standalone arrays, numbers and quoted strings, as well as objects. Keep every event.
Ordering: Sort by event_id in ascending order.
Returnevent_idis_valid
About this problem
You practise checking whether a text column actually contains JSON before anything tries to read it. app_events.payload is a VARCHAR, so it holds a truncated document and a document with trailing junk alongside the good rows, and you return true, false or NULL for each one. The skill is knowing that this check has to happen first, and that it is a different question from “is this an object”.
What makes this worth a problem rather than a footnote is how the obvious alternative fails. Probing with an extraction – payload->>'$.plan' IS NOT NULL – looks like a reasonable way to find out whether a payload can be read, and instead it raises an error on the first malformed row and the query returns nothing at all. That is the failure people meet in production: a report that worked for months stops dead because one producer sent one bad line. The other half of the problem is the definition. A quoted string and an array are valid JSON, even though neither is the object the rest of your query expects, so a shape test like LIKE '{%}' answers a different question and gets ev_4, ev_7 and ev_8 wrong between them. Problem 17 in this set builds on both ideas: it guards every extraction behind this check and then separates “not JSON” from “JSON, but not an object”.
Read the lesson: JSON & Semi-structured (P14) →