8

Lets take some example table peoples , that got only 2 fields: id and data(json).

SELECT data FROM peoples ;
{"name": "Adam","pos":"DBA","age":22 }
{"name": "Alice","pos":"Security","age":33 }
{"name": "Bob","pos":"Manager","age":42 }

I want to create constraint for "pos" field, that must be unique. I've searched over the internet about JSON constraints but no results.

How can I handle this problem ?

  • 6
    JSON is used for schema-less, unstructured data. If you want constraints, you should properly normalize your data. – a_horse_with_no_name Jan 17 '17 at 13:17
  • 5
    'Normalize, normalize, normalize!' (V. I. dezso) – dezso Jan 17 '17 at 14:12
15

First and foremost: I agree with both the comments of @a_horse_with_no_name and @dezso: you should normalize your data. JSON is not for that.

However, if some reason I cannot fathom really makes this an advantage, it is possible:

Create an expression based UNIQUE INDEX:

CREATE UNIQUE INDEX people_data_pos_idx ON peoples( (data->>'pos') ) ;

If, at this point, you try to insert the following piece of data into your table (with an already existing ->>pos):

INSERT INTO peoples(data)
VALUES
    ('{"name": "Eve", "pos":"DBA", "age":34}') ;

You get this as a response:

ERROR: duplicate key value violates unique constraint "people_data_pos_idx"
SQL state: 23505
Detail: Key ((data ->> 'pos'::text))=(DBA) already exists.

NOTE: I've assumed that data.pos will always be a string. If you want to generalize, you can use ( (data->'pos') ) instead. You would index then a JSON(B) expression instead of a text. Check JSON Functions and Operators.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.