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.

Mikael Lepistö · Accepted Answer · 2018-05-31 06:20:40Z

2

.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'])

answered May 31, 2018 at 6:20

Mikael Lepistö

19.8k4 gold badges73 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related