Postgres select rows from table where variable in JSONB array

Question

I'm trying to get rows in my DB where each contains a value in the JSONB column. What I have so far is

    SELECT *
    FROM users
    WHERE roles @> '[{"type": $2 }]'

Roles is a JSONB array and can have about 5 values.

What is the easiest way to get users that have an 'ADMIN' role. Postgres doesn't like the $2 and I can easily modify the query and insert the value as a string but don't want to add an opportunity for SQL injection attacks.

GMB · Accepted Answer · 2020-12-07 21:03:45Z

2

You can use jsonb builder functions to generate a proper jsonb array; this allows properly passing the parameter:

select *
from users
where roles @> jsonb_build_array(jsonb_build_object('type', $2))

answered Dec 7, 2020 at 21:03

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

mgamsjager Over a year ago

Thx so much. Was messing around with other json functions that where slower or not usable with a parameter.

Collectives™ on Stack Overflow

Postgres select rows from table where variable in JSONB array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related