Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE workflows_emails  (
  id INT primary key,
  "to" JSONB
);

CREATE TABLE


INSERT INTO workflows_emails (id, "to") VALUES (1, '[
   {
      "type":"employee",
      "assignment":{
         "something": "..."
      }
   },
   {
      "type":"external",
      "email":"mailbox@foobar.com"
   }
]');

INSERT 0 1


select * from workflows_emails;


id
to


1
[{"type": "employee", "assignment": {"something": "..."}}, {"type": "external", "email": "mailbox@foobar.com"}]



SELECT 1


select id,
  json_agg(
    case
      when element ->> 'type' = 'external' then 
             jsonb_set(element, '{email}', to_jsonb(element ->> 'email' || '.SBX'))
      else element
    end
  ) as new_to
from workflows_emails,
     jsonb_array_elements("to") as element
group by id


id
new_to


1
[{"type": "employee", "assignment": {"something": "..."}}, {"type": "external", "email": "mailbox@foobar.com.SBX"}]



SELECT 1


update workflows_emails set "to" = new_to.new_to
from (
    select id,
  json_agg(
    case
      when element ->> 'type' = 'external' then 
             jsonb_set(element, '{email}', to_jsonb(element ->> 'email' || '.SBX'))
      else element
    end
  ) as new_to
from workflows_emails,
     jsonb_array_elements("to") as element
group by id
) new_to
where new_to.id = workflows_emails.id

UPDATE 1


select * from workflows_emails;


id
to


1
[{"type": "employee", "assignment": {"something": "..."}}, {"type": "external", "email": "mailbox@foobar.com.SBX"}]



SELECT 1