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

I created a PostgreSQL (v10.0) table with a jsonb-array column 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 want add new animals to the first row as follows:

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

However, I want to append only those elements that are not yet in the array. In this case only [chicken, 2].

Answer

probably simplest would be:

t=# with c as (select distinct e,min(o) over (partition by e) o from test, jsonb_array_elements(animals || '["[hamsters, 7]", "[chicken, 2]"]'::jsonb) with ordinality t(e,o) where id =1)
, r as (select jsonb_agg(e order by o) z from c)
t-# update test set animals = z from r where id = 1;
UPDATE 1
t=# select * from test;
 id |                             animals
----+------------------------------------------------------------------
  2 | ["[monkeys, 10]", "[hamsters, 7]", "[foxes, 3]"]
  1 | ["[monkeys, 10]", "[hamsters, 7]", "[foxes, 3]", "[chicken, 2]"]
(2 rows)