ruby sequel gem - how to query arrays with the pg_array extension

Question

I am using the pg_array extension and sequel version 4.1.1.

I have added the extension like this:

Sequel::Database.extension :pg_array

I have created a column like this:

alter_table :emails do
  add_column :references, "text[]", null: true
end

I can load and retrieve arrays into a postgress array column, just like working with normal arrays.

What is not clear from the above link is how do I execute a query based on the values in this array column.

For example, if one row in the emails table contained these values in the references column:

                             references                             
--------------------------------------------------------------------
 {}
 {[email protected]}

How can I query the emails table to find a row that contains a references array value of the above value:

Email.where(references: ????)

Please put the example here in the post, for which you want help ? — Arup Rakshit
– Arup Rakshit, Commented May 2, 2014 at 19:41
Usually you'll want to use operators like @> to determine membership, right? — tadman
– tadman, Commented May 2, 2014 at 19:57

Jeremy Evans · Accepted Answer · 2014-05-03 16:06:55Z

9

Use the pg_array_ops extension:

Sequel.extension :pg_array_ops
Email.where(Sequel.pg_array_op(:references).contains('[email protected]'))

answered May 3, 2014 at 16:06

Jeremy Evans

12.2k31 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sean Dunford Over a year ago

Is this answer still up to date?

okliv Over a year ago

@SeanDunford, for my sequel#5.25.0 & psql#11.5 it works

okliv Over a year ago

what is the correct way to make a schema migration for array fields with index?

Yaniv Over a year ago

Working on Sequel 4, it requires .contains('{[email protected]}')

Adrian Mole · Accepted Answer · 2025-02-22 23:43:44Z

-1

You can try:

ref = '5363f773bccf9'
emails = Email.arel_table
Email.where( emails[ :references ].matches( "%#{ref}%" ))

edited Feb 22 at 23:43

Adrian Mole

52.1k193 gold badges61 silver badges101 bronze badges

answered May 3, 2014 at 9:51

Малъ Скрылевъ

16.5k5 gold badges59 silver badges74 bronze badges

Collectives™ on Stack Overflow

ruby sequel gem - how to query arrays with the pg_array extension

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related