CODEWITHSUNDEEP
arraysjsonpostgresql
mjarzab
mjarzab

Reputation: 251

PostgreSQL - counting the elements in the JSON

I have a JSON type column called "log_data" and the data stored in it is in the format [{"key":"test123123","identity":"[email protected]","identity_type":"email"}].

I want to count how many records for a given value for a given key in json:

Doesn't work

SELECT count (distinct esas_logs.log_id) AS "count" FROM "esas_logs" WHERE log_data->0->>'identity' = '[email protected]'

[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.

Upvotes: 25

Views: 32970

Answers (2)

hyde
hyde

Reputation: 36

According to the doc, you should use the ? operator.

If your column type is JSON:

SELECT COUNT(esas_logs ? 'log_id') FROM esas_logs WHERE ...

If you column is a TEXT or VARCHAR:

SELECT COUNT(esas_logs::jsonb ? 'log_id') FROM esas_logs WHERE ...

Upvotes: 0

Chris Travers
Chris Travers

Reputation: 26464

use json_array_length()

test=# select  json_array_length('[{"key":"test123123","identity":"[email protected]","identity_type":"email"}]');
  json_array_length 
 -------------------
             1
 (1 row)

Upvotes: 34

Related Questions