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

Question

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

Vao Tsun · Accepted Answer · 2017-11-15 10:34:49Z

2

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)

answered Nov 15, 2017 at 10:34

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