CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Mahesh Mesta
Mahesh Mesta

Reputation: 783

Active Record Array array query - to check records that are present in an array

I have an Objective model which has an attribute called as labels whose values are array data type. I need to query all the Objectives whose labels attribute has values that are present in some particular array.

For Example: I have an array

a = ["textile", "blazer"]

the Objective.labels may have values as ["textile, "ramen"]

I need to return all objectives that might have either "textile" or "blazer" as one of their labels array values

I tried the following:

Objective.where("labels @> ARRAY[?]::varchar[]", ["textile"])

This returns some records.Now when I try

Objective.where("labels @> ARRAY[?]::varchar[]", ["textile", "Blazer"])

I expect it to return all Objectives which contains at-least one of the labels array value as textile or blazer.

However, it returns an empty array. Any Solutions?

Upvotes: 0

Views: 683

Answers (1)

Arup Rakshit
Arup Rakshit

Reputation: 118271

Try && overlap operator.

overlap (have elements in common) 

Objective.where("labels && ARRAY[?]::varchar[]", ["textile", "Blazer"])

If you have many rows, a GIN index can speed it up.

Upvotes: 1

Related Questions