How to avoid duplicate values in a PostgreSQL JSON data type?

Question

I have the next example code:

CREATE TABLE books ( id integer, data json );

INSERT INTO books VALUES (1,
  '{ id: 1,id: 1,"name": "Book the First" }');

You can see that id is a field within the format JSON that's duplicated, I understand that PostgreSQL have some feature that avoid that issue, but I can not find it.

That's not a valid JSON though. – zerkms May 12 '16 at 02:16 — zerkms, May 12 '16 at 02:16

score 4 · Answer 1 · answered May 12 '16 at 01:45

4

JSONB will do this automatically: http://www.postgresql.org/docs/9.5/static/datatype-json.html

By contrast, jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.

So just use 9.4 and above and change your table column to jsonb:

CREATE TABLE books ( id integer, data jsonb );

answered May 12 '16 at 01:45

mmm ok JSONB do that, and JSON can't do it? because the hold time I'm using JSON not JSONB maybe that why I can't funded the answer. – EmerBallen May 12 '16 at 01:49
Yeap. Just change it to JSONB. – May 12 '16 at 01:51
@EmersonBallen you can just double-cast, if you really want to use the `json` type: `json_column::jsonb::json`, but the `jsonb` type [has some advantage](http://stackoverflow.com/questions/22654170/explanation-of-jsonb-introduced-by-postgresql) over `json`. – pozs May 12 '16 at 09:15

score 0 · Answer 2 · answered May 12 '16 at 03:12

You can create a function to check for duplicate keys, and set it as a check constraint in your table's json field:

create function jsondupkey(json) returns bool as $$
  select exists (select 1 from json_each($1) group by key having count(*)>1)
$$ language sql;

alter table books alter column data add check (not jsondupkey(data));

How to avoid duplicate values in a PostgreSQL JSON data type?

2 Answers2