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