Test jsonb array to see if it contains one or more elements?

Question

I have a user table with a data jsonb column. In that, I have a cars array that can have 0-N cars in it.

I'm trying to do a query like this to find if the user's cars array contains either Toyota or Mitsubishi, but it's throwing an error

await knex('users.record')
    .where('created_at', '>', '2019-05-21')
    .select('data')
    .whereRaw('cars', ['Toyota', 'Mitsubishi']) // this seems wrong
    .returning('*');

One example user's cars array is ['Toyota', 'Mitsubishi', 'Honda', 'Ford']

Answer 1

Try replacing

.whereRaw('cars', ['Toyota', 'Mitsubishi'])

with

where('cars', 'like','%Toyota%').orWhere('cars', ,like, '%Mitsubishi%')

Answer 2

You can use the jsonb contains operator @>:

SELECT * FROM users
WHERE  created_at > '2019-05-21'
AND   (data @> '{"cars":["Toyota"]}' OR
       data @> '{"cars":["Mitsubishi"]}');

Pure SQL, I am not familiar with knex.
This can be supported with an index. See:

• What's the proper index for querying structures in arrays in Postgres jsonb?

Answer 3

I think it's going to look something like this in Knex (with a little helper function to reduce repetition):

const hasCar = brand => knex.raw(
  `?? @> ?::jsonb`,
  [
    'data',
    JSON.stringify({ cars: [ brand ]})
  ]
)

knex('users.record')
  .where(hasCar('Mitsubishi'))
  .orWhere(hasCar('Toyota'))