query within a json array of strings column in postgres with objection.js or knex.js

Question

I have a column in my table (Reviews) called reviewers.

It was defined via knex: table.json('reviewers').notNullable();

It's just an array of IDs: ['id1', 'id2', 'idn' ]

I would like to query the table based and return all rows that have an occurrence of a string, i.e. 'id2'.

I've tried to do:

Review.query()
  .whereJsonHasAny('reviewers', 'id2')

but I keep getting the error: error: operator does not exist: json ?| text[]

I'm ok with falling back to raw but I can't seem to do this unless I just query the non-json columns and then use logic to filter.

score 1 · Accepted Answer · answered May 31 '18 at 06:20

.whereJsonHasAny works only for checking if an object has keys. Looks like objection.js documentation is wrong about that working for array elements (my bad).

For finding if an array has certain elements you need to use https://vincit.github.io/objection.js/#wherejsonsupersetof

Review.query()
  .isSuperSetOf('reviewers', ['id2'])

query within a json array of strings column in postgres with objection.js or knex.js

1 Answers1