select
true
,new.attributes->'login_country'
from transaction new
where new.attributes->'login_country' in ($high_risk_country)
and new.id = $transactionId
This scenario alerts when transaction attributes login_country is in high_risk_country query template.
Both transaction and person table has attributes field which contains json like (key-value pairs) data. transaction.attributes is currently hstore (PostgreSQL documentation on hstore functions) datatype whereas person.attributes is jsonb (PostgreSQL documentation on json functions). To access values we need to know the key. In the above example the key is login_country.
Scenario alerting customers that use prepaid cards and total value in last 30 days is over 1000 looks like:
select
sum(past.amount) > 1000
,sum(past.amount
from transaction new
join transaction past on new.person_id=past.person_id
join person on person.id=new.person_id
where new.id = $transactionId
and past.customer_id = $organisationId
and past.timestamp >= new.timestamp - '30 days'::interval
and past.timestamp <= new.timestamp
and person.customer_id = $organisationId
and person.attributes->>'product'='PREPAID_CARD'
To make queries more performant then using EXISTS can be one of the strategies. Same scenario could be written:
select
sum(past.amount) > 1000
,sum(past.amount
from transaction new
join transaction past on new.person_id=past.person_id
where new.id = $transactionId
and past.customer_id = $organisationId
and past.timestamp >= new.timestamp - '30 days'::interval
and past.timestamp <= new.timestamp
and exists (select 1
from person
where person.customer_id = $organisationId
and person.attributes->>'product'='PREPAID_CARD')
and person.id = new.person_id)
As you see there are numerous ways to achieve same goal it maybe mainly personal preference or convenience question which format to prefer.