PostgreSQL中计算嵌套JSON数组内指定ID的时长总和

假设你的表（示例表名your_table）里有一个JSON类型字段（示例字段名task_data），存储的嵌套JSON数组格式类似：

[
{"id": 55, "days": [{"duration": "3:00"}, {"duration": "4:00"}]},
{"id": 56, "days": [{"duration": "5:00"}, {"duration": "4:00"}]}
]

下面是直接可用的查询语句，能按ID分组计算所有duration的总和：

SELECT
  parent_obj->>'id' AS id,
  TO_CHAR(
    SUM(MAKE_INTERVAL(
      hours => (SPLIT_PART(day_obj->>'duration', ':', 1))::INT,
      minutes => (SPLIT_PART(day_obj->>'duration', ':', 2))::INT
    )),
    'HH24:MI'
  ) AS duration
FROM
  your_table,
  json_array_elements(your_table.task_data) AS parent_obj,
  json_array_elements(parent_obj->'days') AS day_obj
GROUP BY
  parent_obj->>'id';

关键步骤说明 #

• json_array_elements()：两次调用分别展开最外层数组和每个ID对应的days子数组，把嵌套结构扁平化。
• SPLIT_PART()：将duration的"小时:分钟"字符串拆分为数值，方便转换为时间间隔。
• MAKE_INTERVAL()：把拆分后的小时、分钟转为PostgreSQL原生的时间间隔类型，支持直接求和。
• SUM() + GROUP BY：按ID分组，累加所有天数的时长。
• TO_CHAR()：把求和后的时间间隔格式化为你需要的HH:MI字符串格式。

执行后会得到类似这样的结果：

id  duration
55  07:00
56  09:00

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