SQL Query Where Array is in Array Field

Question

I have a table which has an array field called countries So lets say for example in this table called country_list i have the following records

Name         Countries
Asia         ['Malaysia, Singapore, Hong Kong]
Worldwide    ['Malaysia, Singapore, Hong Kong, Croatia, USA]

Now if i want to query this table to find out which records have ['Malaysia, 'Singapore'], how do i write the query in my ruby console.

I tried to do this but it didnt work

CountryList.where("countries IN (['Malaysia', 'Singapore'])") but it didnt work as expected.

Any help is appreciated

This a curious approach. I wonder why you didn't make Country and Region and associate them. Life would be somewhat easier. — jvillian
– jvillian, Commented Jun 5, 2018 at 16:36
@jvillian because i am getting my list of regions and countries from the ISO3166 global configuration and each of those records is tied to a company so meaning there is a company id field for each of those records. Making a Country and Region table will require manual entry of records into these table which i feel is nt necessary — Kingsley Simon
– Kingsley Simon, Commented Jun 5, 2018 at 17:01

rahul mishra · Accepted Answer · 2018-06-05 18:28:01Z

1

Try out this:

countries = ['Malaysia', 'Singapore']
query = CountryList.where(":name = ANY(country_list.countries)", name: countries[0])

countries[1..-1].each do |c|
  query = query.or(CountryList.where(":name = ANY(country_list.countries)", name: c))
 end
query

Have Checked its working

Thanks!

edited Jun 5, 2018 at 18:28

answered Jun 5, 2018 at 18:22

rahul mishra

1,49011 silver badges19 bronze badges

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

1 Comment

engineersmnky Over a year ago

This is the correct answer. In order to find out if a value is contained in a postgres array column you must search all the values in the array using value = ANY(column). This means that if you want to search for multiple values you will need need to search them independently using OR. If you wanted to make sure that both Malaysia and Singapore existed you could use AND instead via query = query.where(":name = ANY(country_list.countries)", name: c) inside the loop

Rajesh V · Accepted Answer · 2018-06-05 16:35:53Z

0

There is no direct approach to query serialized data. But the other work around is by selecting/filtering from all the results. Eg:

Model.all.select{|s| (s.countries &['Malaysia', 'Singapore']).present?}

answered Jun 5, 2018 at 16:35

Rajesh V

1564 bronze badges

Collectives™ on Stack Overflow

SQL Query Where Array is in Array Field

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related