Search code examples
ruby-on-railsarrayspostgresqlhstore

Hstore PostgreSQL Array comparision to any in array, Rails 3

Thank you for your time:

I have a group of records (users to be specific) with an Hstore hash saved in a "data" field. The thing is I want to make a single SQL to fetch the users from that group of users with hash hstore saved property in that "data" field matching another array of strings values like this:

data: { "foo" => "", "bar" => "bars", "baz" => "bazz" }

array of strings to match ["foos", "bazz"]

NOTE: I want to match the values, NOT the keys

every user has a set of "data" foo/bar/bas properties, and I send an array of "keys" from the hash to extract and compare to the second "values" array but I can't seem to make a single SQL matching. So far this is all I have:

keys = ["foo", "baz"]
values_to_match = ["bars", "bazz"]
users.where("users.data -> ARRAY[?] ILIKE ANY (ARRAY[?])", keys, values_to_match)
Solution

  • I found a simple SQL solution:

    users.where("(users.data -> ARRAY[?]) && (ARRAY[?])", keys, values_to_match)

    Seems like Intersection of arrays compares correctly the values from the data field of users even tho the documentation says it returns a hash like:

    hstore -> ARRAY[keys] | result: { "value", "value" }

    thank you for your time to all those who took their time to answer or read, if anyone could clarify why this happens I would be thankfull