Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

drop table if exists students;
create table students(student_id serial primary key, name text, emails jsonb);
insert into students (name, emails) values ('John Doe', '[]');
1 rows affected

create or replace function jsonb_unique_array(jsonb)
returns jsonb language sql immutable as $$
	select jsonb_agg(distinct value)
	from jsonb_array_elements($1) 
$$;
update students 
set emails = jsonb_unique_array(emails || '["j.doe@email.com"]'::jsonb)
where student_id=1
and not emails @> '["j.doe@email.com"]'::jsonb
returning *


student_id
name
emails


1
John Doe
["j.doe@email.com"]


update students 
set emails = jsonb_unique_array(emails || '["j.doe@email.com", "john@email.com"]'::jsonb)
where student_id=1
and not emails @> '["j.doe@email.com", "john@email.com"]'::jsonb
returning *


student_id
name
emails


1
John Doe
["j.doe@email.com", "john@email.com"]