How to create a constraint for unique (not including order) JSONB column in postgresql

Question

I am looking for a way to create a constraint that will verify the values in a JSONB column are unique, however i need them to also be unique when compared without taking order into account, so JSONB comparison / hashing is not valid for this case.

In order to check if 2 JSONB objects are equal, i check if they contain each other

select t.a @> t.b and  t.a <@ t.b
from (
SELECT
    '{"foo": 42, "bar": ["value", 5]}'::jsonb as a,
    '{"bar": [5, "value"], "foo": 42}'::jsonb as b
) as t

Specifically i am looking for an SQLAlchemy solution, but a raw postgresql solution will probably help a lot.

foo and bar will be reordered automatically, but I doubt that there is a good solution. — Laurenz Albe
– Laurenz Albe, Commented Jul 14, 2021 at 13:26
@LaurenzAlbe keys are reordered automatically however lists are not? is there a way to reorder the lists automatically on insert? I don't need to maintain original ordering, but i also don't want to order it in application code — Alonme
– Alonme, Commented Jul 14, 2021 at 13:58

Alonme · Accepted Answer · 2021-07-18 10:00:45Z

My solution was to add a trigger that calls a function that checks if there is already a jsonb which contains and is contained by the one that is being inserted

trigger:

create trigger trigger1
before insert
on json_table
for each row
execute procedure func;

function:

create function func returns trigger
language plpgsql
as
$$
declare
same_json json_table%rowtype;
BEGIN
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')  THEN
        select * from json_table
        into same_json
        where NEW.json @> json_table.json and NEW.json <@ json_table.json;

        IF FOUND then
            RAISE NOTICE 'Not inserting json, an equal json is already in the table: id=%', same_json.id;
            return NULL;
        END IF;
    END IF;
    RETURN NEW;
END;
$$;

I then created a manual alembic migration which added the trigger and function - see https://github.com/sqlalchemy/alembic/issues/504#issuecomment-441887901

Collectives™ on Stack Overflow

How to create a constraint for unique (not including order) JSONB column in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related