Postgres trigger function if statement on jsonb array

Question

I'm trying to insert into another table if a user has a certain role. The roles are currently stored on the user object as a json array [{'type': 'ADMIN'}]

My code is as follows

BEGIN
    IF NEW.roles->'type' @> '"ADMIN"' THEN
        // INSERT INTO new_table
    END IF;
    RETURN NEW;
END;

The row never gets inserted if the role is there. Is there a way to do IF statements on jsonb?

user330315 · Accepted Answer · 2020-09-22 20:28:15Z

1

As roles is an array, you need to test for an array element:

IF NEW.roles @> '[{"type": "ADMIN"}]'

Alternatively, if you know the array only contains a single element, you can access the value by index:

IF NEW.roles -> 0 ->> 'type' = 'ADMIN'

answered Sep 22, 2020 at 20:28

user330315

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

Collectives™ on Stack Overflow

Postgres trigger function if statement on jsonb array

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