select case
when count(ubo) > 3 then 4
when count(ubo) <= 3 and count(ubo) > 2 then 2
else 0
end as risk_score
, count(ubo) as number_ubos
from (select jsonb_array_elements(attributes->'company_beneficial_owners') as ubo
from person p where customer_id = $organisationId
and p.id = $personId
) ubos
The above risk rule looks at person attribute company_beneficial_owners (ubo) and assigns risk score 4 in the case where number of ubo’s is more than three and risk score 2 when the number is 3; when its less than 3 the score is 0. In our exemplar case company_beneficial_owners attribute is a list of 2 elements and looks like this:
[
{
"first_name": "Howard",
"last_name": "Wolowitz",
"dob": "1988-07-06",
"citizenship": "IL",
"is_pep": "Yes"
},
{
"first_name": "Raj",
"last_name": "Koothrappali",
"dob": "1989-05-06",
"citizenship": "IN",
"is_pep": "No"
}
]
To treat this data structure as a list we have to call the json_array_elements function and as an argument use attributes::json -> 'company_beneficial_owners'
In the case when clause we simply check the length of the list to get the number of UBO-s.
If we are interested if someone is a PEP, we can leave everything else the same, cause we are doing our checks on the same underlying data and only rewrite the select clause.
SELECT
case
when ubo->> 'is_pep' = 'Yes' then 4
when ubo->> 'is_pep' = 'No' then 0
else 0
end,
ubo ->> 'is_pep'
from (select jsonb_array_elements(attributes->'company_beneficial_owners') as ubo
from person p
where customer_id = $organisationId
and p.id = $personId
) ubos
order by 1 desc limit 1