PostgreSQL: append element to jsonb array only if element is unique

Question

I created a PostgreSQL (v10.0) as follows.

CREATE TABLE test (id INT, animals jsonb)

INSERT INTO test VALUES
   (1, '["[monkeys, 10]", "[hamsters, 7]", "[foxes, 3]"]'),
   (2, '["[monkeys, 10]", "[hamsters, 7]", "[foxes, 3]"]')

Then I append a new animal as follows:

UPDATE test
SET animals = animals || '["[hamsters, 7]"]'::jsonb
WHERE id = 1;

However, I want to append the element only if the element is not yet in the array.

Answer 1

you can just use additional WHERE clause:

UPDATE test
SET animals = animals || '["[hamsters, 7]"]'::jsonb
WHERE id = 1 AND animals ~ '"\[hamsters, \d\]"'

Here I don't use jsonb, but rather just regular expression. to avoid nesting array and because "[hamsters, 7]" itself won't be array anyway - just a string, so I compare with string operators...