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.

Vao Tsun · Accepted Answer · 2017-11-15 09:49:56Z

2

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...

answered Nov 15, 2017 at 9:49

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related