Is it possible to query a specific index of a postgres array with Rails 4?

Question

With a database column migrated like this:

add_column :documents, :array_column, :string, array: true

I understand it's possible to do this to query any element of the array:

Document.where("'foo' = ANY (array_column)")

My question is whether or not I can specifically query the second (or any other single) element of an array?

mu is too short · Accepted Answer · 2013-10-15 18:37:32Z

You can use the usual array indexing notation but remember that SQL arrays are 1-based rather than 0-based:

Document.where("array_column[2] = 'foo'")

The fine manual has more on accessing arrays.

1 Answer