How can I query Rails ActiveRecord data stored in arrays

Question

I have a rails model call MentorData and it has an attribute called os_usage. The oses are stored in an array like so ['apple', 'linux'].

To recap:

$ MentorData.first.os_usage
=> ['apple',  'linux']

I am looking to be able to query the data for all MentorData that includes the os_usage of apple, but when I search MentorData.where(os_usage: 'apple') I only get the mentors who can only use apple and not apple and linux. I need to search in some way that checks if apple is included in the array.

I have also tried the following.

MentorData.where('os_usage like ?', 'apple’)
MentorData.where('os_usage contains ?', 'apple’)
MentorData.where('os_usage contains @>ARRAY[?]', 'apple')

Is it possible to query data in ActiveRecord by attributes that have an array or items?

The database is on Postgres if that helps in providing a more raw search query.

Answer 1

Here are the examples given in the current Rails Edge Guides for PostgreSQL:

# db/migrate/20140207133952_create_books.rb
create_table :books do |t|
  t.string 'title'
  t.string 'tags', array: true
  t.integer 'ratings', array: true
end
add_index :books, :tags, using: 'gin'
add_index :books, :ratings, using: 'gin'
 
# app/models/book.rb
class Book < ActiveRecord::Base
end
 
# Usage
Book.create title: "Brave New World",
            tags: ["fantasy", "fiction"],
            ratings: [4, 5]
 
## Books for a single tag
Book.where("'fantasy' = ANY (tags)")
 
## Books for multiple tags
Book.where("tags @> ARRAY[?]::varchar[]", ["fantasy", "fiction"])
 
## Books with 3 or more ratings
Book.where("array_length(ratings, 1) >= 3")

Answer 2

Have you tried MentorData.where("'apple' = ANY (os_usage)")?

Answer 3

Maybe you should detach the os_usage array from your model and make it a separate table.

In ActiveRecord world you will get something like the following code:

class MentorData < ActiveRecord::Base
  ..
  has_and_belongs_to_many :os_usage
  ..
end

class OsUsage < ActiveRecord::Base
  ..
  has_and_belongs_to_many :mentors_data
  ..
end

Creating a many_to_many relationship between this two models, allows you to query easily and avoid duplications. This technique is called normalization.

Using this new design you have your collection of os_usage made by objects instead of strings

MentorData.first.os_usage
# => [#<OsUsage:....>, #<OsUsage:...>]

Which you can convert easy into the old array of strings

MentorData.first.os_usage.map(&:name)
# => ['apple',  'linux']

In addition, you can query the data for all MentorData that includes the os_usage of apple:

MentorData.joins(:os_usages).where('os_usages.name' => 'apple')

And also query all the MentorData records for an OsUsage:

OsUsage.where(name: 'apple').mentors_data

I hope you find it useful :)

Answer 4

For like queries, you need %% to indicate that text can appear on the left or right of your search.

So, try

MentorData.where('os_usage LIKE "%apple%"')

and see if that works.

It is a wild card search, but omitting the % operates like =

See this question: SQL LIKE with no wildcards the same as '='?

This assumes os_usage is a serialized array, where the column backing that data is a string, and rails deserializes when instantiating your MentorData

Edit: I'd find out how your db is storing the array, so maybe you could do

"%'apple'%"

to make sure that it doesn't select oses with apple just contained in the name.