How to use a group by clause on an ActiveRecord query using a Hstore key?

Question

I am working on a RoR application. I use Postgres with Hstore. I want to use a group query using a Hstore key. How can this be done?

Answer 1

Yes, of course you can. A GROUP BY clause is a pretty general tool so you can group by any expression you'd like. So, given data like this:

=> select * from with_hstore order by id;
 id |         h          
----+--------------------
  1 | "a"=>"6"
  2 | "a"=>"2"
  3 | "b"=>"1"
  4 | "a"=>"b"
  5 | "x"=>"z", "y"=>"6"
  6 | "a"=>NULL

You can group by a key's value using h -> key:

=> select h -> 'a', count(*) from with_hstore group by h -> 'a';
 ?column? | count 
----------+-------
          |     3
 2        |     1
 6        |     1
 b        |     1

Note that a missing key and a NULL value end up coming out the same here. You can even group by the existence of a key using exist:

=> select exist(h, 'a'), count(*) from with_hstore group by exist(h, 'a');
 exist | count 
-------+-------
 f     |     2
 t     |     4

You wouldn't want to group by (h -> 'a') is not null though as you can have NULL values that that test won't differentiate between an explicit NULL and an hstore without the key in question; of course, that might be what you want so maybe you do want to group by (h -> 'a') is not null.

ActiveRecord will let you group by anything that the database can handle by passing your group condition as an SQL snippet:

Model.group("h -> 'a'")...
Model.group("exist(h, 'a')")...
...