PostgreSQL实现按应用聚合分组并嵌套标题统计的JSON结构查询求助

各位PostgreSQL大佬好，我现在卡在一个复杂的JSON聚合查询上了，想请大家帮忙看看！

场景与需求 #

我手里有两张表：

• 一张是用户应用使用时长的原始数据表，记录了每个应用/网页的启动时间、结束时间，还有存在app_info字段里的JSON数据（包含app_name、path、domain_site、url、title这些信息）
• 另一张是granules表，是通过原始表生成的时间粒度分段表，每个记录包含employee_id、date、granula_start（粒度起始时间）、granula_end（粒度结束时间）

我需要实现的查询效果是：针对granules表的每一条粒度记录，生成一个嵌套的JSON数组，满足以下要求：

1. 按应用的app_name和domain_site分组，统计每个应用在当前粒度内的总使用秒数count_seconds
2. 每个应用的统计项里，还要嵌套一个titles数组，包含该应用下各个网页/窗口的url、title以及对应的使用秒数
3. 最终每个粒度记录对应一个这样的JSON结构数组

举个具体的例子，用户在10分钟粒度内使用Chrome浏览器访问了两个Stack Overflow页面，期望得到的JSON结构是：

[
  {
    "app_name": "googlechrome",
    "path": "C:\\ProgramFiles\\google\\googlechrome.exe",
    "domain_site": "stackoverflow.com",
    "count_seconds": 540,
    "titles": [
      {
        "count_seconds": 320,
        "url": "https://stackoverflow.com/questions/40978290/construct-json-object-from-query-with-group-by-sum",
        "title": "Construct json object from query with group by / sum"
      },
      {
        "count_seconds": 220,
        "url": "https://stackoverflow.com/questions/43117033/aggregate-function-calls-cannot-be-nested-postgresql",
        "title": "aggregate function calls cannot be nested postgresql"
      }
    ]
  }
]

如果同个粒度内用户用了多个应用，每个应用都要生成这样的统计项。

我尝试的查询（报错） #

我自己写了下面的查询，但运行时出现错误，没能得到预期的嵌套结构：

select 
  employee_id, 
  date, 
  granula_start, 
  granula_end,
  (select 
     array_agg(json_build_object(
       'seconds', SUM( 
         case when end_time > granula_end then 
           ((EXTRACT(MINUTE FROM granula_end) - EXTRACT(MINUTE FROM start_time))*60) 
         else 
           (case when EXTRACT(MINUTE FROM start_time) = EXTRACT(MINUTE FROM end_time) then 
              EXTRACT(SECOND FROM end_time) - EXTRACT(SECOND FROM start_time) 
            else 
              (EXTRACT(MINUTE FROM end_time) - EXTRACT(MINUTE FROM start_time)) * 60 
            end ) 
         end ),
       'domain_site', m.app_info::jsonb->'domain_site',
       'app_name', m.app_info::jsonb->'app_name',
       'app_type', m.app_info::jsonb->'app_type'
     )) as app_info
   from pps.my_temp m 
   where start_time >= granula_start and start_time <= granula_end 
   group by m.app_info::jsonb->'domain_site', m.app_info::jsonb->'app_name'
  )
from granules

想请大家帮忙看看这个查询哪里有问题，或者给出正确的实现思路，让我能得到符合需求的嵌套JSON结构！

内容来源于stack exchange