Updating a jsonb array value with constraints in Postgres
Solution 1:
You can use a subquery with json_agg:
select jsonb_set(t.data, '{owners}',
(select jsonb_agg(case when v.value::text = '"admin"' or v.value::text ~ 'google\.com"' then replace(v.value::text, '"', '') else md5(substring(v.value::text from '^"([\w\.\-]+)@'))||'@'||md5(substring(v.value::text from '@([\w\.\-]+)"$')) end)
from jsonb_array_elements(t.data -> 'owners') v))
from vals t
For the purposes of demonstration, the hash used is md5.