CODEWITHSUNDEEP
sqlarraysjsonpostgresqlwhere-clause
Steve Fitzsimons
Steve Fitzsimons

Reputation: 3904

Postgres select rows from table where variable in JSONB array

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.

Upvotes: 1

Views: 358

Answers (1)

GMB
GMB

Reputation: 222422

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

Upvotes: 2

Related Questions