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

Question

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?

Arup Rakshit · Accepted Answer · 2018-07-31 12:56:19Z

1

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.

edited Jul 31, 2018 at 12:56

answered Jul 31, 2018 at 12:29

Arup Rakshit

119k30 gold badges270 silver badges328 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related