Search code examples
ruby-on-railsarrayspostgresql-9.3hstoreruby-on-rails-4.1

Rails 4 Querying a hstore array?

I have a answers hstore array column in the reviews table in a postgres 9.3.2 Database on rails 4.1.4

add_column :reviews, :answers, :hstore, array:true

with values like that:

=> [{"qid"=>"299", "val"=>"1", "field_type"=>"rating_field"}, {"qid"=>"300", "val"=>"2", "field_type"=>"rating_field"}]

I'm trying to get all values from qid == 299, so the output should be 1, or an array when there are multiple values

I tried: Review.where("answers -> 'qid' = '299'")["val"]

Some ideas?

Thanks, Patrick

Solution

  • Via this answer

    https://stackoverflow.com/a/10117278/1436131

    A posgresql Array is just a string, so you can't index it. Instead you might want to look at Posgresql native support for JSON, instead of another join table as they suggest.

    http://www.postgresql.org/docs/9.3/static/functions-json.html

    The following answer gives you a good idea of how to do this.

    https://stackoverflow.com/a/22290601/1436131