Write a query to return every event in app_events with event_id and the nested device.os value from payload as plain text, without JSON quotes.
Return NULL if device or its os key is absent. Include all event types. Key names are case-sensitive: OS does not match os.
Ordering: Sort by event_id in ascending order.
Returnevent_idos
About this problem
You practise reading a value out of a nested JSON object and getting it back as text you can actually use. Every event in app_events may carry a device object with an os inside it, and you return that value for each event, with NULL wherever the path does not lead anywhere. The skill is picking the right extraction operator and then leaving the gaps alone.
The mistake that costs people later is using -> instead of ->>. Both find the value, but -> hands back a JSON value, so what lands in your column is "ios" with the quotation marks included. Nothing errors, the grid looks right at a glance, and then a filter on os = 'ios' matches nothing and a join on the column finds no rows. The second temptation is to tidy the data while you read it. ev_5 spells the key OS, and JSON keys are case-sensitive, so the honest answer is NULL; a coalesce across both spellings fills the cell in and quietly decides that a different key means the same thing, which is a decision for the people who own the feed, not for this query.
Read the lesson: JSON & Semi-structured (P14) →