How to query array columns in Rails 4?

rails update array column
rails 5 array column
rails migration json column
rails migration array
rails store array in database
rails array_agg
rails postgres array
rails where like array

I can't find any good articles about how to query array columns in Rails. I came across the need to query an Array column in Rails.

I found from an article teaching how to do basic query here.

Let's follow the example in the article where Book covers many subjects and subjects is stored as an array column:

add_column :books, :subjects, :text, array: true, default: []

Query books that contains a certain subject - e.g. History

Book.where("'history' = ANY (subjects)")

Query books that contains all listed subjects - e.g. Finance AND Business AND Accounting

Book.where("subjects @> ?", "{Finance,Business,Accounting}")

I wonder how I can do the following?

Query books that contains any of the listed subjects - e.g. Fiction OR Biography

Query books that doesn't contain a certain subject - e.g. NOT Physics

Query books that doesn't contain ANY of the subjects - e.g. NOT (Physics OR Chemistry OR Biology)

And is there any Rails way of doing the above queries?

  1. Usually, associations are a preferable way of approaching the problem:

    Book has_many :subjects # or has_one/has_and_belongs_to_many

    Subject belongs_to :book # or has_and_belongs_to_many

    And then just create a table subjects, save all your subjects there and you're set up.

  2. Your queries:

Query books that contains any of the listed subjects - e.g. Fiction OR Biography

Book.find_by_sql "SELECT * FROM books WHERE 'Fiction' = ANY (subjects) OR 'Biography' = ANY (subjects)"

Query books that doesn't contain a certain subject - e.g. NOT Physics

Book.where.not("subjects @> ?", "{Physics}")

Query books that doesn't contain ANY of the subjects - e.g. NOT (Physics OR Chemistry OR Biology)

Book.find_by_sql "SELECT * FROM books WHERE books NOT IN (SELECT * FROM books WHERE 'Physics' = ANY (subjects) OR 'Chemistry' = ANY (subjects) OR 'Biology' = ANY (subjects)"

Rails 4 & the PostgreSQL Array data type (Example), To query your records you want to use PSQL's ANY and/or ALL methods: SELECT * FROM users WHERE 'foo@bar.com' = ANY (emails); SELECT * FROM users WHERE 'foo@bar.com' = ALL (emails); PostgreSQL Arrays. Postgres has built-in array-type columns, which we can take advantage of in our Rails 4.X apps with the postgres_ext gem. What does this mean for our Rails apps? If we can store arrays in a single database column, then our database and application have the same understanding of the structure of the data.

For,

Query books that contains any of the listed subjects - e.g. Fiction OR Biography

Book.where("subjects &&  ?", "{Fiction,Biography}")

Query books that doesn't contain a certain subject - e.g. NOT Physics

Book.where("subjects <>  ?", "{Physics}")

Query books that don't contain ANY of the subjects - e.g. NOT (Physics OR Chemistry OR Biology)

Book.where.not("subjects &&  ?", "{Physics,Chemistry,Biology}")

You can see the array functions of Postgres for reference.

https://www.postgresql.org/docs/8.2/functions-array.html

Rails 4 and PostgreSQL Arrays � Plataformatec Blog, Rails 4 supports arrays fields for PostgreSQL in a nice way, although it except for the column tags which will have the Array type in this case. Rails 4.1+ Rails 4.1 treats default_scope just as a regular scope. The default scope (if you have any) is included in the where_values result and inject(:or) will add or statement between the default scope and your wheres. That's bad. To solve that, you just need to unscope the query.

I might be missing something, but this is why you don't normally store this type of data in the database as an array (serialized or not), it makes complex queries much more difficult. I'd suggest creating additional model(s) which would allow you to use rails magic to do the kind of queries you're talking about.

Rails + Postgres Array + ANY LIKE, Ruby on Rails has a good support for Postgres Array type. This query returns zero results, because the pattern containing % has to be on the� Active Record 4 now has support for PG's Array data-type. Here's how you might use it to give users multiple email addresses: Migration: class AddEmailsToUser < ActiveRecord::Migration def change add_column :users, :emails, :string, array: true, default: '{}' end end

Ruby on Rails, add_column :products, :colors, array: true, default: []. Add an index for an array column: add_index :products, :colors, using: 'gin'. PDF - Download Ruby on Rails � How to find records where column is not null or empty in Rails 4 or 5 We all know that especially in legacy applications sometimes our database columns are not that well maintained. So we need to query for, or exclude rows containing nil / NULL and empty strings ( "" ) as well.

Active Record and PostgreSQL — Ruby on Rails Guides, Bytea; Array; Hstore; JSON and JSONB; Range Types; Composite Types; Enumerated It describes how to properly set up Active Record for PostgreSQL. street VARCHAR(90) ); SQL create_table :contacts do |t| t.column :address, of the all enums you have, you should call this query in bin/rails db or psql console:. ARRAY ARRAY(subquery) Description. The ARRAY function returns an ARRAY with one element for each row in a subquery. If subquery produces a SQL table, the table must have exactly one column. Each element in the output ARRAY is the value of the single column of a row in the table.

How to start using Arrays in Rails with PostgreSQL, How to start using Arrays in Rails with PostgreSQL Now is the time to add some subjects for books and then query them. type, and a few extra CPU cycles to check the length when storing into a length-constrained column. I am using Postgres' json data type but want to do a query/ordering with data that is nested within the json. I want to order or query with .where on the json data type. For example, I want to query for users that have a follower count > 500 or I want to order by follower or following count. Thanks! Example:

Comments
  • Hmm, I saw that postgres and Rails 4 came out with the array column. I guess sometimes the performance is actually better when storing as array columns? I may ask a new question to see if anyone can evaluate when to use array column and when to use associations
  • then why is array column created? I assume it is rails intention to adopt some noSQL approach of storing data? I am no expert in it and would like to learn more, I think if Rails 4 / Postgres decides to implement it, there must be a reason? We can offload this discussion to another thread. => stackoverflow.com/questions/31569511/…
  • @ChrisYeung Despite the results of the separate question let me know, if this answer was helpful with regards to queries
  • Bad advice in the first paragraph - there are valid reasons to store array data in database. If there wasn't a good enough use case for it, the data type wouldn't exist (although I agree in OP's case it should be an association). However, +1 for actually providing the solution of how to query the array datatype and answering OP's question.