sql - ActiveRecord Query to find JSON Value Array for a given Key -

in rails 4 have table json type column. have method works so

def self.that_match_property(key: "default", value: "default")   where("properties ->> ? = ?", key, value) end 

so model.first.properties {"name": "bill", "user_id": "1"} can fine.

model.that_match_property(key: "name", value: "bill")  

and record or records match on key/value pair in json properties column.

but... let's want value array of ids. have...

user1.properties = {"name": "bill", "user_id": "1"} user2.properties = {"name": "ted", "user_id": "2"} user3.properties = {"name": "rufus", "user_id": "3"} 

now have bill_and_ted_ids = ["1", "2"]

i want able this:

model.that_match_property(key: "user_id", value: bill_and_ted_ids) 

but doesn't work. pass in array of ids active record query , converts proper sql.

what correct way above json data type in rails?

maybe helpfull:

def build_where_query_string_from_hash(hash)   hash.collect |k,v|     if v.is_a?(array)       string = v.collect{|e| "(properties ->> '#{k}'='#{e}')"}.join(" or ")       "(#{string})"     else       "(properties ->> #'{k}'='#{v}')"     end   end.join(" , ") end 

then can pass: build_where_query_string_from_hash(user_id: [1,2], name: ["bill", "ted"])

which returns:

((properties ->> 'user_id'='1') or (properties ->> 'user_id'='2')) , ((properties ->> 'name'='bill') or (properties ->> 'name'='ted'))`