What’s the best way to “append” to a list of strings stored in a JSONB column in a table PostgreSQL such that duplicates are not stored

Say there is a table employee

create table employee (
    emp_id varchar(100),
    tags jsonb NOT NULL DEFAULT '[]' :: jsonb;
);

One of the rows has the following information in the tags column

["TagA", "TagB", "TagC"]

Currently to update the column with additional values I perform the following query

UPDATE employees SET tags = employees.tags || to_jsonb('["TagD", "TagA"]'::jsonb) where emp_id = 'EMP-111'

However this adds duplicates to the cell and makes it look like

["TagA", "TagB", "TagC", "TagD", "TagA"]

which is less than desirable for my use case. Currently to circumvent this I’ve handled duplicates in post-processing at the application layer.

Is there an efficient way to remove duplicates like “TagA” at runtime?

Answer

You can use jsonb_agg with distinct in a subquery:

update employees set tags = coalesce((select jsonb_agg(distinct v.value #>> '{}') 
    from jsonb_array_elements(employees.tags || to_jsonb('["TagD", "TagA"]'::jsonb)) v), '[]'::jsonb) 
where emp_id = 'EMP-111'

See fiddle.