Rails query objects by key value of hash saved to column?

Question

I have 2 objects, Visitors and Events. Visitors have multiple Events. An event stores parameters like this...

#<Event id: 5466, event_type: "Visit", visitor_token: "c26a6098-64bb-4652-9aa0-e41c214f42cb", contact_id: 657, data: {"url"=>"http://widget.powerpress.co/", "title"=>"Home (light) | Widget"}, created_at: "2015-12-17 14:51:53", updated_at: "2015-12-17 14:51:53", website_id: 2>

As you can see, there is a serialized text column called data that stores a hash with more data.

I need to find out if a visitor has visited a certain page, which would be very simple if the url parameter were it's own column, or if the hash were an hstore column, however it wasn't originally set up that way and it's a part of the saved hash.

Here's my attempted rails queries...

visitor.events.where("data -> url = :value", value: 'http://widget.powerpress.co/')

visitor.events.where("data like ?", "{'url' => 'http://widget.powerpress.co/'}")

visitor.events.where("data -> :key LIKE :value", :key => 'url', :value => "%http://widget.powerpress.co/%")

How does one properly query postgres to find objects that have a hash that contains a key with a specific value?

Answer 1

I suspect you're not looking for the right string. It should be "url"=>"http://widget.powerpress.co/", so:

visitor.events.where("data like ?", '%"url"=>"http://widget.powerpress.co/"%')

Check the right value directly in DB.