GROUP BY JSON类型字段时出现PostgreSQL错误，寻求解决方法

错误原因 #

PostgreSQL的json类型没有内置相等比较运算符，而GROUP BY操作需要通过相等判断来对数据分组，因此直接用json类型字段作为分组依据会触发ERROR: could not identify an equality operator for type json错误。

同时原查询存在额外问题：SELECT语句中的e.date和e.time_spent既不在GROUP BY列表中，也未使用聚合函数，这在PostgreSQL默认配置下会触发语法错误，需要一并修正。

修复方案 #

以下两种方法可解决JSON分组问题，同时修正SELECT字段的聚合逻辑：

方案1：转换为jsonb类型分组 #

jsonb是PostgreSQL支持的二进制JSON类型，内置相等比较运算符，适合分组操作。使用->>可直接提取URL的文本值：

SELECT 
  MAX(e.date) AS latest_date, -- 根据需求选择聚合方式，比如取该URL的最新记录日期
  e.headers::jsonb->>'url' AS url, 
  SUM(e.time_spent) AS total_time_spent -- 统计每个URL的总耗时
FROM some_table e 
JOIN some_table a ON e.key = a.key 
WHERE a.name = 'firefox' 
  AND e.date BETWEEN '2022-11-15' AND '2022-11-21' 
GROUP BY e.headers::jsonb->>'url' 
ORDER BY total_time_spent DESC 
LIMIT 30;

方案2：将JSON提取值转为文本类型分组 #

直接把从JSON中提取的url转换为text类型，利用文本类型的相等运算符完成分组：

SELECT 
  MAX(e.date) AS latest_date,
  (e.headers::json->'url')::text AS url, 
  SUM(e.time_spent) AS total_time_spent
FROM some_table e 
JOIN some_table a ON e.key = a.key 
WHERE a.name = 'firefox' 
  AND e.date BETWEEN '2022-11-15' AND '2022-11-21' 
GROUP BY (e.headers::json->'url')::text 
ORDER BY total_time_spent DESC 
LIMIT 30;

补充说明 #

• 聚合函数可按需替换：SUM(e.time_spent)可换成AVG（平均耗时）、MAX（单次最大耗时）等；若不需要日期字段，可直接移除MAX(e.date) AS latest_date。
• 若headers字段本身就是jsonb类型，可省略::jsonb转换步骤。

内容的提问来源于stack exchange，提问作者Rupesh Biswas