PostgreSQL - 统计JSON中的元素数量

Question

我有一个叫做“log_data”的JSON类型的列，其中存储的数据格式为[{"key":"test123123","identity":"[e-mail]","identity_type":"email"}]。

我想要统计给定键和给定值的记录数:

不起作用

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

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

- mjarzab

---

“esas_logs.log_id”列的数据类型是什么？ - Abelisto

Answer 1

使用json_array_length()

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

- Chris Travers

---

如果字段设置为 jsonb，则应使用 jsonb_array_length() 方法。 - hanmari

Answer 2

根据文档，您应该使用?运算符。

如果您的列类型是JSON：

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

如果您的列是TEXT或VARCHAR类型：

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

- hyde