PostgreSQL - counting the elements in the JSON
<p>I have a JSON type column called "log_data" and the data stored in it is in the format <code>[{"key":"test123123","identity":"user@test.it","identity_type":"email"}]</code>.</p>

<p>I want to count how many records for a given value for a given key in json: </p>

<p><strong>Doesn't work</strong></p>

<pre><code>SELECT count (distinct esas_logs.log_id) AS "count" FROM "esas_logs" WHERE log_data-&gt;0-&gt;&gt;'identity' = 'user@test.it'

[2016-06-30 13:59:18] [42883] ERROR: operator does not exist: json = unknown
  HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
</code></pre>