CODEWITHSUNDEEP
sqlnode.jstypescriptpostgresqlknex.js
Mohd Ahmad
Mohd Ahmad

Reputation: 1

how to search values in json field in sql (postgresql)

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

Upvotes: 0

Views: 124

Answers (3)

Jose Carvalho
Jose Carvalho

Reputation: 59

I would do like this:

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

Upvotes: 0

Jannchie
Jannchie

Reputation: 1008

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

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

Upvotes: 1

user330315
user330315

Reputation:

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

Upvotes: 0

Related Questions