How do I check if an array column contains a value in Postgres using Objection JS?

Question

I tried using an array of integers, then an array of strings. However, I keep getting the same error:

ERROR:  operator does not exist: jsonb ?| integer[]

My query looks like this:

Bet.query()
    .select(
        'id', 
        'status'
    )
    .whereJsonSupersetOf('participants_ids', [userId])
    .range()
    .limit(10)   
    .orderBy('id', 'DESC')
    .throwIfNotFound();

This is how the arrays are stored:

Each user has a screen where they can see their own bets against another users. In order to list bets for a logged in user, I need to check the participants_ids column. This is an array that contains the ids for the 2 users betting against each other.

The purpose of my query is to return a list of bets where the current user's Id is contained inside each bet's participants_ids array.

Originally, I tried user .where() and .orWhere() to check if the current user's id was either the bet host's id, or the bet challenger's id. This didn't give me the result I wanted though. So I decided an array column would be much better.

I can't seem to get this to work though. I've looked at a few posts, but they seem to be arrays of objects rather than arrays of ints or strings. I simply want to check the participants_ids array column contains the userId I am passing into the query.

I am also using Knex JS.

Any idea what I could be doing wrong here?

Thanks in advance.

Mikael Lepistö · Accepted Answer · 2020-07-31 14:32:18Z

3

.whereJsonXXX methods works only for postgresql jsonb columns.

For querying arrays column types you need to use array operators https://www.postgresql.org/docs/12/functions-array.html

Bet.query()
    .select(
        'id', 
        'status'
    )
    .where('participants_ids', '@>', [userId])
    .range()
    .limit(10)   
    .orderBy('id', 'DESC')
    .throwIfNotFound();

Or maybe .where('participants_ids', '@>', val([userId]).asArray().castTo('integer[]')) if the array is not passed properly in the first example.

answered Jul 31, 2020 at 14:32

Mikael Lepistö

19.8k4 gold badges73 silver badges74 bronze badges

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

1 Comment

LondonGuy Over a year ago

Thanks. I changed the array type to integer and used your first example. It worked great.

Collectives™ on Stack Overflow

How do I check if an array column contains a value in Postgres using Objection JS?

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