Deviopath › PostgreSQL

Purchases from JSON Events

Medium · JSON

In a jsonb column, payload ->> 'key' returns the value of a key as text (or NULL when the key is missing). Cast it to compare numbers: (payload ->> 'amount')::numeric.

From events with name = 'purchase', keep those whose payload has currency EUR and an amount of at least 20. Return user_id, item (the payload's item) and amount (the payload's amount cast to numeric(10, 2)). Sort by amount descending, then user_id ascending.

Next: Popular Tags