PostgreSQL：将数组元素频次统计为JSON对象的优化写法问询

你的需求可以通过更简洁的方式实现，核心思路是直接对展开后的数组元素分组统计，再聚合为JSON对象，避免冗余的子查询和JOIN操作：

基础版本（针对示例数组） #

WITH items (item) AS (SELECT UNNEST(ARRAY['a','b','c','a','a','a','c']))
SELECT json_object_agg(item, count) AS item_counts
FROM (
  SELECT item, COUNT(*) AS count
  FROM items
  GROUP BY item
) sub;

执行结果和原查询一致：{ "a" : 4, "b" : 1, "c" : 2 }

针对表中数组列的场景 #

如果数组来自某张表（比如my_table的array_col列），可以直接展开列并统计：

-- 统计全表所有数组元素的全局次数
SELECT json_object_agg(item, count) AS item_counts
FROM (
  SELECT unnest(array_col) AS item, COUNT(*) AS count
  FROM my_table
  GROUP BY item
) sub;

如果需要按每行生成对应数组的统计JSON（比如表有主键id）：

SELECT id, json_object_agg(item, count) AS item_counts
FROM (
  SELECT id, unnest(array_col) AS item, COUNT(*) AS count
  FROM my_table
  GROUP BY id, item
) sub
GROUP BY id;

优化说明 #

原查询的冗余点在于额外用了DISTINCT子查询再JOIN，而GROUP BY本身就会自动聚合唯一值，完全不需要这一步。优化后的写法：

• 逻辑更清晰，减少嵌套层级
• 避免不必要的JOIN操作，性能更优（数据量越大差异越明显）
• 后续嵌入复杂查询时，更容易维护和扩展

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