Force uniqueness in JSONB array-CodePudding

Let's say I have a table students with a column type jsonb where I store a list with students' additional emails. A student row looks like this

student_id	name	emails
1	John Doe	[[email protected]]

I'm using the following query to update the emails column:

UPDATE students SET emails = emails || '["[email protected]"]'::jsonb
                     WHERE student_id=1
                     AND NOT emails @> '["[email protected]"]'::jsonb;

Once the column emails is filled, if I reuse query above with the parameter ["[email protected]", "[email protected]"], the column emails would be update with repeated value:

student_id	name	emails
1	Student 1	[[email protected], [email protected], [email protected]]

Is there a way to make sure that in the column emails I'll always have a jsonb list with only unique values ?

CodePudding user response：

Use this handy function which removes duplicates from a jsonb array:

create or replace function jsonb_unique_array(jsonb)
returns jsonb language sql immutable as $$
    select jsonb_agg(distinct value)
    from jsonb_array_elements($1) 
$$;

Your update statement may look like this:

update students 
set emails = jsonb_unique_array(emails || '["[email protected]", "[email protected]"]'::jsonb)
where student_id=1
and not emails @> '["[email protected]", "[email protected]"]'::jsonb

Test it in db<>fiddle.