lets say i have a column named keyword with values like ['wood', 'grass', 'tree', 'plant'],
now how do I query that if this array contains wood or grass or both of them
my solution (here I am using a text string and searching through it)
select * from table where keywords_column ~* 'wood';
but it is limited to one word only. It would be great if I would get solution in knex.js or Adonis Lucid
I thought this might work, but I didn't test it:
select * from table where keywords_column ~* 'wood' or keywords_column ~* 'grass';
You can use the contains operator ?|
select *
from the_table
where keywords_column ?| array['wood', 'grass'];
This assumes that keywords_columns is defined as jsonb (which it should be if you store JSON values). If it's not, you need to cast it keywords_column::jsonb
I would do like this:
SELECT * from table WHERE column SIMILAR TO '(wood |grass |tree |plant )%';
