How to search with jsonb operator "@>" in feathers.js/knex service

Question

Using feathersJs/Knex and Postgresql.

The (simplified) SQL query is this:

SELECT * FROM projects WHERE team_members @> '{"members":[{"id": 1}]}';

How can I implement this request in feathers/knex service? Everything I tried throws a Bad Request error.

I tried to use rawQuery but could not make it work. I tried with regular query but it use by default the = operator instead of @> (as it is normal).

I'm considering building a separate service on server but I'm wondering if there is no easier way to do it. I just started with feathersJs, I'm sure I'm missing something and it has to be very simple.

Answer 1

Knex doesn't have any specific support for that operator. Objection.js which is built on top of knex supports jsonb operators, so it might be easier to use in a long run https://vincit.github.io/objection.js/api/query-builder/find-methods.html#wherejsonsupersetof.

That being said with knex you can do that like this:

knex('projects').whereRaw(`?? @> ?::jsonb`, [
  'team_members', 
  JSON.stringify({members:[{id: 1}]})
])

Which creates following query:

{ method: 'select',
  bindings: [ '{"members":[{"id":1}]}' ],
  sql: 'select * from "projects" where "team_members" @> ?::jsonb' }