0

I am using ahoy gem in rails application to get the analytics data which is present in ahoy_events table i want to group the rows based on the id present in hash of properties column.

Table looks like this https://s10.postimg.org/j9e5ylvuh/data_table.png

Have tried query like Ahoy::Event.group("properties REGEXP '[{,]\"id\"[,}]' ") this returns only the first row.I know the syntax is correct but i am unable to find where i am getting wrong.Have search it a lot and tried different combination permutations of queries but could not find the correct one.Any suggesstions will be a great help.

yogeshmanjhi
  • 335
  • 1
  • 4
  • 17
  • Would it be possible to use PostgreSQL (9.4+) instead? As it supports JSON data typing you probably could find a niftier solution using it. Check this question for instance: http://stackoverflow.com/questions/39261409/postgres-group-by-on-jsonb-inner-field – fabriciofreitag Apr 06 '17 at 05:05
  • @fabriciofreitag currently using mysql database so cant change it. is there any easy way to group rows based on hash present in table? – yogeshmanjhi Apr 06 '17 at 05:28
  • What version of MySQL? Newer versions of MySQL/MariaDB have better JSON support. Or don't hide the thing inside JSON. – Rick James Apr 06 '17 at 14:52

0 Answers0