1

In my Rails 5 app, I'm using the Ahoy gem which uses a jsonb field (called properties) that I don't know how to query.

A sample of the data in the properties field:

"{\"id\":\"11\"}"

What I'm trying to do is:

  1. Find the records created in the last seven days (using the time field)
  2. Group the records by the id in the properties field
  3. Order the records based on which id had the most records

I tried this:

@foo = Ahoy::Event.where(name: "Viewed Product").where(time: 7.days.ago..Time.now).group("properties(id)").order('COUNT("properties(id)") DESC')

but I received the error, PG::UndefinedColumn: ERROR: column "properties(id)" doe not exist

I found this somewhat similar question in the issues for the gem, so I tried breaking the query into parts:

@foo = Ahoy::Event.where(name: "Viewed Product").where(time: 7.days.ago..Time.now)
@foo.group("properties REGEXP '[{,]\"id\"...).count

but then my IDE gives the error unterminated string meets the end of file.

Can someone please help me determine what I'm doing wrong?

yellowreign
  • 3,528
  • 8
  • 43
  • 80

1 Answers1

0

If you are using PostgreSQL, I believe that you would have to refer to json columns like this: .group("properties ->> 'id'")

Jawad Khawaja
  • 756
  • 3
  • 12
  • Yes, I saw a post mentioning that. When I tried that I received `PG::GroupingError: ERROR: column "ahoy_events.id" must appear in the GROUP BY clause or be used in an aggregate function` – yellowreign Mar 01 '17 at 06:21
  • That is understandable. Check this link ( http://bernardoamc.github.io/sql/2015/05/04/group-by-non-aggregate-columns/ ) for details. You may want to modify your query as per your use case. However, in any case you will have to use ```properties ->> 'id'``` if you plan to query on the jsonb column. – Jawad Khawaja Mar 01 '17 at 06:37