I wonder if it is possible to achieve uniqueness in an array of objects, only using one field of the object as the determinant if the object is unique or not with jsonb.
An example of what I mean:
I want to ensure that if the field of type jsonb looks like this:
"[{"x":"a", "timestamp": "2016-12-26T12:09:43.901Z"}]"
then I want to have a constraint that forbids me to put another entry with "x":"a" regardless of what the timestamp(or any other field for that matter) is on the new object I’m trying to enter
3
Answers
The unique way to do so is to have a JSON typed datatype for your column with a JSON schema with "uniqueItems": true .
Unfortunately PostgreSQL does not accepts such syntax to enforce checks. You have to do it outside the PG Cluster…
This can be achieved with a check constraint.
This prevents multiple array elements with
"x": "a"
. If you want to prevent multiple array elements with the keyx
(regardless of the value), you can use:You can do this using a
CHECK
constraint and a helper function:(online fiddle)