sql - How to constrain that JSON/JSONB values in a column be completely different? -

for table like:

create table example (     totally_unique jsonb ); 

how can constrain values keys in totally_unique must different? keys , values can strings, can't write individual constraints each possible key.

in other words, if {"a": "one", "b": "two"} in table, want prevent inserting {"a": "one", "b": "three"} because value totally_unique->>'a' = 'one' exists.

a unique constraint isn't sufficient this, don't see kind of constraint or index work.

there no built-in method guarantee unique key/value pairs inside json values across table, neither json nor jsonb.

but can achieve goal helper table , index. considering outermost level of json values. not prepared nested values.

solution jsonb

requires postgres 9.4, obviously.
works json in postgres 9.3, too, after minor modifications.

table layout

create table example (   example_id     serial primary key , totally_unique jsonb not null );  create table example_key (   key   text , value text , primary key (key, value) ); 

trigger function & trigger

create or replace function trg_example_insupdelbef()   returns trigger $func$ begin    -- split update delete & insert simplify    if tg_op = 'update'       if old.totally_unique distinct new.totally_unique  -- keep going       else return new;  -- exit, nothing       end if;    end if;     if tg_op in ('delete', 'update')       delete example_key k       using  jsonb_each_text(old.totally_unique) j(key, value)        j.key = k.key       ,    j.value = k.value;        if tg_op = 'delete' return old;  -- exit, done       end if;    end if;     insert example_key(key, value)    select *      jsonb_each_text(new.totally_unique) j;     return new; end $func$ language plpgsql;  create trigger example_insupdelbef before insert or delete or update of totally_unique on example each row execute procedure trg_example_insupdelbef(); 

sql fiddle demonstrating insert / update / delete.
_{note sqlfiddle.com doesn't provide postgres 9.4 cluster, yet. demo emulates json on pg 9.3.}

the key function handle jsonb jsonb_each_text(), need, since values supposed text.

closely related answer postgres array column more explanation:

• can postgresql have uniqueness constraint on array elements?

also consider "righteous path" of normalization laid out there. applies here well.

this not unbreakable unique constraint, since triggers can circumvented other triggers , more deactivated, if don't of sort, constraint enforced @ times.

note in particular that, per documentation:

truncate not fire on delete triggers might exist tables. fire on truncate triggers.

if plan truncate example, make sure truncate example_key well, or create trigger that.

performance should decently good. if totally_unique column holds many keys , typically few change per update, might pay have separate logic tg_op = 'update' in trigger: distill change set between old , new, , apply example_key.