How can I search for arrays that contain strings found in my search key?

Question

Say I have a Product table with a json array attribute called "name". For example, Product.first.name == ["large", "black", "hoodie"]. I want to search through my database for Products with names that contain words in my search query. So if I type in "large hoodie", Product.first should be returned in the results.

So first I have to turn the search key into an array of strings:

def search
  search_array = params[:search].split(" ")
  results = #???

but how can I search for Products with names that include values also contained in search_array? I've found documentation on how to search for values within arrays, but not on how to search for arrays themselves.

Is the column name a jsonb or just a serialized text? – Roman Kiselenko Oct 30 '17 at 05:39 — Roman Kiselenko, Oct 30 '17 at 05:39

score 3 · Accepted Answer · answered Oct 30 '17 at 05:39

You can simply use, @> (contains) operator.

select * from products;
 id |  name   |              tags              |         created_at         |         updated_at         
----+---------+--------------------------------+----------------------------+----------------------------
  3 | T-Shirt | {clothing,summer}              | 2017-10-30 05:28:19.394888 | 2017-10-30 05:28:19.394888
  4 | Sweater | {clothing,winter,large,hoodie} | 2017-10-30 05:28:38.189589 | 2017-10-30 05:28:38.189589
(2 rows)


select * from products where tags @> '{large, hoodie}';
 id |  name   |              tags              |         created_at         |         updated_at         
----+---------+--------------------------------+----------------------------+----------------------------
  4 | Sweater | {clothing,winter,large,hoodie} | 2017-10-30 05:28:38.189589 | 2017-10-30 05:28:38.189589
(1 row)

Or, as an AR query,

2.3.1 :002 > Product.where("tags @> '{large, hoodie}'")
  Product Load (0.4ms)  SELECT "products".* FROM "products" WHERE (tags @> '{large, hoodie}')
 => #<ActiveRecord::Relation [#<Product id: 4, name: "Sweater", tags: ["clothing", "winter", "large", "hoodie"], created_at: "2017-10-30 05:28:38", updated_at: "2017-10-30 05:28:38">]>

Awesome! Do you know if the LIKE/ILIKE methods can be integrated with this? — Joe Morano, Nov 04 '17 at 06:40
however have a look up those threads. https://dba.stackexchange.com/questions/117740/postgresql-like-query-on-array-field https://stackoverflow.com/questions/7222106/postgres-query-of-an-array-using-like https://stackoverflow.com/questions/4928054/postgresql-wildcard-like-for-any-of-a-list-of-words — marmeladze, Nov 04 '17 at 15:24

rony36 · Answer 2 · 2017-10-30T02:41:34.863

1

Okay, as you are using postgresql, you can use gem pg_search.

Add search scope in model:

include PgSearch
pg_search_scope :search_on_text_columns,
                 against: %i(name),
                 using: { tsearch: { prefix: true } }

For more details check out the documentation. Cheers!

edited Oct 30 '17 at 02:41

answered Oct 30 '17 at 02:35

rony36

3,277
1
30
42

How can I search for arrays that contain strings found in my search key?

2 Answers2