How to use unique constraint for json type in postgresql

Question

How can I set a unique constraint for "json" column in postgresql? Unique means this for me: A json object is unique if every key matches and every value of the json object matches.

Answer 1

This is not possible with your somewhat outdated version 9.4

Postgres 9.5 introduced a different JSON type, named jsonb which does support an equality operator. So if you really, really need this, you will have to upgrade to a more up-to-date version (e.g. 10 or 11)

The json data type preserves the ordering of the input value and thus makes comparison really complicated (at least not efficient).

The jsonb type normalizes the key/values and stores the data in binary format which enables efficient equality checking.

e.g.

'{"foo": 42, "bar": "value"}'::jsonb = '{"bar": "value", "foo": 42}'::jsonb

returns true. The same expression with a cast to json results in an error.

So the following DDL works:

create table data 
(
  id        serial primary key,
  payload   jsonb,
  unique (payload)
);

---

However, there is a limit on how large an index entry is allowed to be (something around 2000 byte). So it might happen that inserting data into such a table fails not because of the violation of the unique constraint, but because the JSON value is too big.

---

One option with Postgres 9.4 would be to create a unique index on the MD5 hash of the JSON value.

create unique index on data ( md5(payload::text) );

But that will only work if you always specify the keys inside the JSON in exactly the same order. Because the md5 hash of '{"foo": 42, "bar": "value"}' is different than the one for '{"bar": "value"}, "foo": 42'

---

Some questions with solutions to index large text values:

• https://dba.stackexchange.com/questions/144575
• https://dba.stackexchange.com/questions/69161
• https://dba.stackexchange.com/questions/69170
• https://dba.stackexchange.com/questions/217087/