PostgreSQL中如何对jsonb字段内的值求和并使用聚合函数

你原来的查询逻辑是先按id和json的key分组，计算每个key的求和值再重新拼成json对象，所以得到的结果是键值对结构，不符合你要的单条记录所有值求和的需求。调整后的查询如下：

SELECT
  id,
  SUM(value::numeric) AS total_sum
FROM mytable t,
     jsonb_each_text(total)
GROUP BY id;

执行后就能得到你期望的每行id对应total字段所有数值总和的结果。

如果要后续对这些行级总和做min、max、avg等聚合运算，可以把上面的查询作为子查询嵌套，示例如下：

SELECT
  AVG(row_total) AS avg_all_total,
  MIN(row_total) AS min_all_total,
  MAX(row_total) AS max_all_total
FROM (
  SELECT SUM(value::numeric) AS row_total
  FROM mytable t, jsonb_each_text(total)
  GROUP BY id
) AS row_sums;

如果你的表中存在total字段为null或者空json的记录，上述查询会直接忽略这些行。如果需要保留这些行并将求和结果记为0，可以改用左连接的写法：

SELECT
  id,
  COALESCE(SUM(value::numeric), 0) AS total_sum
FROM mytable t
LEFT JOIN jsonb_each_text(total) ON true
GROUP BY id;

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