Force uniqueness in JSONB array

user_4647895 0
Viewed 34

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 [j.doe@email.com]

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

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

Once the column emails is filled, if I reuse query above with the parameter ["j.doe@email.com", "john@email.com"], the column emails would be update with repeated value:

student_id name emails
1 Student 1 [j.doe@email.com, j.doe@email.com, john@email.com]

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

jsonb unique postgresql arrays json
1 Answers

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 || '["j.doe@email.com", "john@email.com"]'::jsonb)
where student_id=1
and not emails @> '["j.doe@email.com", "john@email.com"]'::jsonb

Test it in db<>fiddle.

user_19957380
Related