Postgres jsonb ensure unique field in array of objects-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

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);

CodePudding user response：

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