Write a query to return each purchase event in app_events with event_id and numeric amount from either supported payload shape: a nested payment object with an amount key, or a top-level key named payment.amount.
A literal dot in a key name is part of that key; it does not indicate nesting. Return NULL if neither shape supplies a value or the value cannot be read as a number.
Ordering: Sort by event_id in ascending order.
Returnevent_idamount
About this problem
You practise reading the same logical field out of two different document shapes, where one of them uses a key name that a JSONPath would otherwise misread. Purchases arrive either with a nested payment object or with a flat payment.amount key, and you return one numeric amount column that handles both. The skill is knowing that a dot in a path is punctuation, and how to say you meant it literally.
The failure here is one-sided and therefore convincing. Write only $.payment.amount and you get the right answer for every event the new producer sent, with NULLs for the old one – which looks exactly like “those events had no payment”. Write only the quoted path and you get the mirror image. Either way the query runs, the column has numbers in it, and roughly half the data is missing for a reason nobody can see from the output. The second thing to get right is the conversion: ->> always returns text, so 900 and "750.00" both arrive as strings, and comparing or summing them without casting gives character-order results.
Read the lesson: JSON & Semi-structured (P14) →