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.

Answer 1

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.