Postgres: Set unique index on JSON field of array type

Question

There is a table like following:

CREATE TABLE test (my_field jsonb NOT NULL)

The my_field has a key like collection which stores values in varchar[] format:

{my_field: {collection: ['a', 'b', 'c']}}

I know that it is possible to set unique index for simple json values. For instance, for {my_field: {name: 'Someone'}}, uniqe constraint could be set. (gave it a try already)

I have tried exclude using gist a lot, but I've got nothing so far.

Is it possible to set unique(considering overlapping) constraint in this situation?

Thanks in advance.

you want a UK ofr array?.. ordered or not?.. ['a','b'] <> ['b','a']?.. — Vao Tsun
– Vao Tsun, Commented Apr 24, 2017 at 13:27
It is not matter of ordering, uniqueness is what I am looking for. — Kaveh
– Kaveh, Commented Apr 24, 2017 at 17:54

Vao Tsun · Accepted Answer · 2017-04-24 19:28:41Z

0

If it is not matter of ordering then simple index will work:

so=# CREATE TABLE test (my_field jsonb NOT NULL);
CREATE TABLE
so=# insert into test select '{"my_field": {"collection": ["a", "b", "c"]}}';
INSERT 0 1
so=# create unique index uk_c on test ((my_field->'my_field'->>'collection'));
CREATE INDEX
so=# insert into test select '{"my_field": {"collection": ["a", "b", "c"]}}';
ERROR:  duplicate key value violates unique constraint "uk_c"
DETAIL:  Key (((my_field -> 'my_field'::text) ->> 'collection'::text))=(["a", "b", "c"]) already exists.

answered Apr 24, 2017 at 19:28

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

2 Comments

Kaveh Over a year ago

What if I insert just ["a", "b"]?

Kaveh Over a year ago

I need something like overlapping (&&). I appreciate your concern man.

Collectives™ on Stack Overflow

Postgres: Set unique index on JSON field of array type

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related