Rails 4 Querying a hstore array?

Question

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

score 1 · Answer 1 · edited May 23 '17 at 11:44

You can write a scope for this.
I'm in the process of creating some additional scopes ontop of Surus specifically for hstore arrays.: https://github.com/jackc/surus

This gem helped me on the way, it already provides you with the scopes to work on regular hstores.

specifically for hstore arrays. Doing this kind of work on hstore arrays can be quite tricky,

Basically we would want to unnest the array, cast this back to hstore, and then use the hstore operand @> to check if right hand key=>value pair exists within left hand subselect. The only way to achieve this is with a subquery.

Review.where(" exists ( SELECT * FROM  ( SELECT  hstore(unnest(reviews.answers)))  x(item)      WHERE x.item  @> '"qid" => "299"')");

This should work nicely. CHeck out my fork, https://github.com/milankubin/surus It will provide you with two scopes for now:

Model.hstore_array_has_any(column, value) # will look for anything matching value inside array Model.hstore_array_has_pairs(column, hash) # the answer you are looking for :)

=> #<ActiveRecord::Relation []>
2.1.2 :002 > Lead.hstore_array_has_pairs(:m3_data, {"name" => "3-zitsbank"})
Lead Load (2.0ms)  SELECT "leads".* FROM "leads"  WHERE ( exists (  SELECT * FROM  (     SELECT hstore(unnest("m3_data")))  x(item)  WHERE x.item  @> '"name"=>"3-zitsbank"'))
=> #<ActiveRecord::Relation [#<Lead id: 35655,[.......]

It's not true that hstore does not support indexes. You can index on individual keys and even when nested within a hstore array. It's not easy though, and will require you to write your own index planner when adding the index.

Take a look at this answer, this might get you started: PostgreSQL - query against GIN index of HSTORE value

I haven't ventured into this world yet, but It's certainly possible.

score 0 · Accepted Answer · edited May 23 '17 at 11:52

0

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

edited May 23 '17 at 11:52

Community

1
1

answered Aug 10 '14 at 21:51

rovermicrover

1,453
1
15
21

ok, thanks, I'll try the last link, I'm trying to generate some data fore sidekick: http://ankane.github.io/chartkick/ – Patrick Winkler Aug 11 '14 at 16:44

score 0 · Answer 3 · answered Jan 24 '16 at 17:06

0

Try this: Review.where("answers ->> 'qid' = '299'").first.answers['val']

answered Jan 24 '16 at 17:06

Elad Amsalem

1,490
1
12
14

Rails 4 Querying a hstore array?

3 Answers3