0

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.

SharpBCD
  • 547
  • 1
  • 7
  • 25

1 Answers1

2

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' }
Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70
  • Great answer, thank you. It's just that I'm not a fan of ORM in general. I prefer writing pure SQL and optimize it for the specific DB. In such case, what do you recommend? Should I write a custom service in Feathers and just run a raw query? Maybe return the result in the same manner for consistency even if I'm not sure at this moment how to implement pagination - for example. – SharpBCD May 07 '19 at 20:37