Postgres jsonb ensure unique field in array of objects

Question

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

Try to use unique indexes postgresql.org/docs/current/indexes-expressional.html — samuelcolt
– samuelcolt, Commented Nov 15, 2022 at 8:05
CREATE UNIQUE INDEX your_index ON table_name( (data->>'x') ); — samuelcolt
– samuelcolt, Commented Nov 15, 2022 at 8:09
It's unclear to me if you want to prevent duplicates in a single array (=column value), or duplicates across rows in the table. — user330315
– user330315, Commented Nov 15, 2022 at 8:20
prevent duplicates(based on only one field in an object) in a single array @a_horse_with_no_name — D. O.
– D. O., Commented Nov 15, 2022 at 8:20

user330315 · Accepted Answer · 2022-11-15 08:26:10Z

4

This can be achieved with a check constraint.

create table the_table
(
  id int primary key, 
  data jsonb,
  constraint check_single_xa 
     check (jsonb_array_length(jsonb_path_query_array(data, '$[*] ? (@.x == "a").x')) <= 1)
);

This prevents multiple array elements with "x": "a". If you want to prevent multiple array elements with the key x (regardless of the value), you can use:

  constraint check_single_xa 
     check (jsonb_array_length(jsonb_path_query_array(data, '$[*].x')) <= 1)

answered Nov 15, 2022 at 8:26

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

D. O. Over a year ago

Ok, I will try this out a bit later and get back to you :)

D. O. Over a year ago

Looking at this though I want to allow the same key "x" just no duplicate values for it among any of the objects of the array. I don't specifically care about "a", I just don't want multiple "a"'s. Does this constraint do that?

user330315 Over a year ago

@D.O.: that's the second check constraint in my ansser

D. O. Over a year ago

Ok I checked it now, your second constraint seems to do what I want. I'll accept this as answer :)

Bergi · Accepted Answer · 2022-11-15 08:37:02Z

3

You can do this using a CHECK constraint and a helper function:

CREATE FUNCTION is_x_unique(arr jsonb) RETURNS boolean
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    RETURN NOT EXISTS(SELECT * FROM jsonb_array_elements(arr) GROUP BY value->'x' HAVING COUNT(*) > 1);

^{(online fiddle)}

ALTER TABLE example ADD CONSTRAINT unique_x_in_data CHECK is_x_unique(data);

answered Nov 15, 2022 at 8:37

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Comments

SQLpro · Accepted Answer · 2022-11-15 08:16:33Z

-1

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...

edited Nov 15, 2022 at 8:16

user330315

answered Nov 15, 2022 at 8:05

SQLpro

5,3121 gold badge9 silver badges19 bronze badges

7 Comments

user330315 Over a year ago

This is wrong, this can easily be expressed using a check constraint. And there are multiple extensions that provide JSON schema validation.

SQLpro Over a year ago

Of course you can externalise some JSON data and add a SQL CONSTRAINT on it. But it is not the easiest way nor the performer one... And passing by a function will lead by some extraoverhead and no possibilities to parallelise the execution of the INSERT/UPDATE queries. The example given by Bergi encapsulates one of the worst function in terme of performances (jsonb_array_elements) inside another function...

user330315 Over a year ago

There is no need to pass anything to a function. A plain CHECK constraint will do this. A check constraint will most certainly not prevent concurrent inserts or updates on the table, not even with the function from Bergi's answer. So how would this be done in your beloved sql serVER?

user330315 Over a year ago

My answer contains such a constraint

user330315 Over a year ago

Yes, two built-in functions. Why would that be a problem? It's no different to a check constraint like say lower(name) = name or trim(name) <> ''

|

Collectives™ on Stack Overflow

Postgres jsonb ensure unique field in array of objects

3 Answers 3

4 Comments

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related