title: Rails - Querying a JSON Field
body: <p>In my Rails 5 app, I'm using the <a href="https://github.com/ankane/ahoy" rel="nofollow noreferrer">Ahoy gem</a> which uses a jsonb field (called <code>properties</code>) that I don't know how to query.</p>
  
  <p>A sample of the data in the <code>properties</code> field:</p>
  
  <pre><code>"{\"id\":\"11\"}"
  </code></pre>
  
  <p>What I'm trying to do is: </p>
  
  <ol>
  <li>Find the records created in the last seven days (using the <code>time</code> field)</li>
  <li>Group the records by the <code>id</code> in the <code>properties</code> field</li>
  <li>Order the records based on which <code>id</code> had the most records</li>
  </ol>
  
  <p>I tried this:</p>
  
  <pre><code>@foo = Ahoy::Event.where(name: "Viewed Product").where(time: 7.days.ago..Time.now).group("properties(id)").order('COUNT("properties(id)") DESC')
  </code></pre>
  
  <p>but I received the error, <code>PG::UndefinedColumn: ERROR:  column "properties(id)" doe not exist</code></p>
  
  <p>I found this <a href="https://github.com/ankane/ahoy/issues/198" rel="nofollow noreferrer">somewhat similar question</a> in the issues for the gem, so I tried breaking the query into parts:</p>
  
  <pre><code>@foo = Ahoy::Event.where(name: "Viewed Product").where(time: 7.days.ago..Time.now)
  @foo.group("properties REGEXP '[{,]\"id\"...).count
  </code></pre>
  
  <p>but then my IDE gives the error <code>unterminated string meets the end of file</code>.</p>
  
  <p>Can someone please help me determine what I'm doing wrong?</p>