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 ?

  • 8
    JSON is used for schema-less, unstructured data. If you want constraints, you should properly normalize your data.
    – user1822
    Commented Jan 17, 2017 at 13:17
  • 5
    'Normalize, normalize, normalize!' (V. I. dezso) Commented Jan 17, 2017 at 14:12
  • Nosql dbs such as Mongo support this. Why not do it in sql server? I think it's useful
    – Zach Smith
    Commented May 5, 2021 at 19:19

1 Answer 1


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)
    ('{"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.

  • That might result in a sparse table.
    – baash05
    Commented May 5, 2022 at 6:27
  • Can you please elaborate @baash05? What is "sparse table" in this context, and what issue does this imply? I'm considering this same solution suggested by @joanolo but I'd like to also weigh the downsides properly
    – tmilar
    Commented Jul 4, 2022 at 22:45
  • @tmilar Did you find an answer to that question?
    – mowwwalker
    Commented May 13, 2023 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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