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, Apr 24 '17 at 13:27
It is not matter of ordering, uniqueness is what I am looking for. — Kaveh, Apr 24 '17 at 17:54

score 0 · Answer 1 · answered Apr 24 '17 at 19:28

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 '17 at 19:28

Vao Tsun

47,234
13
100
132

What if I insert just ["a", "b"]? – Kaveh Apr 25 '17 at 05:23
I need something like overlapping (&&). I appreciate your concern man. – Kaveh Apr 25 '17 at 05:47

Postgres: Set unique index on JSON field of array type

1 Answers1