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.

Answer 1

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