15

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 ?

3
  • 8
    JSON is used for schema-less, unstructured data. If you want constraints, you should properly normalize your data. Jan 17, 2017 at 13:17
  • 5
    'Normalize, normalize, normalize!' (V. I. dezso)
    – dezso
    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
    May 5, 2021 at 19:19

1 Answer 1

29

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.

2
  • That might result in a sparse table.
    – baash05
    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
    Jul 4, 2022 at 22:45

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.