Build a Clean Document per Event

Write a query to return each purchase event in app_events with event_id and doc, a JSON document containing exactly these keys in this order:

plan: The payload’s plan as text.

amount: Its amount as a number, or JSON null if unreadable.

os: Its nested device.os as text.

Include all three keys even when a value is unavailable. Missing values become JSON null rather than omitted keys. Preserve readable decimal amounts as numbers.

Ordering: Sort by event_id in ascending order.

Returnevent_iddoc

Columns

app_events4 columns

columntype

event_idVARCHAR

user_idVARCHAR

event_typeVARCHAR

payloadVARCHAR

Sample data & expected output

6 rows · 4 columns

event_iduser_idevent_typepayload

ev_1u_1purchase{"plan":"pro","amount":900,"device":{"os":"ios"}}

ev_2u_2purchase{"plan":"team","amount":1000}

ev_3u_3purchase{"amount":120,"device":{"os":"android"}}

ev_4u_1purchase{"plan":"basic","amount":"90","device":{"os":"web"}}

ev_5u_4signup{"plan":"pro","amount":1,"device":{"os":"ios"}}

ev_6u_2purchase{"plan":"pro","amount":"n/a"}

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise assembling a JSON document rather than taking one apart, which is what a transformation step does when it hands a consistent shape to the next system. For each purchase you build a document with the same three keys every time, converting the amount to a real number and letting anything absent become an explicit null. The skill is using the JSON builder and understanding what it does with the gaps.

The failure that will actually bite is string concatenation. It looks like the simple option, and then ev_2 has no device, the || chain hits a NULL, and the entire document becomes NULL – or worse, the quoting is slightly off and you have produced text that is not valid JSON, which nothing notices until a consumer tries to parse it. The other thing to get right is the type. ->> hands you text, so unless you cast, ev_1’s amount goes in as "900" with quotes and the document is subtly wrong in a way that only shows up when someone downstream tries to add it up.

Read the lesson: JSON & Semi-structured (P14) →

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows