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

CodePudding user response：

I thought this might work, but I didn't test it:

select * from table where keywords_column ~* 'wood' or keywords_column ~* 'grass';

CodePudding user response：

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

Online example

CodePudding user response：

I would do like this:

SELECT * from table WHERE column SIMILAR TO '(wood |grass |tree |plant )%';