I've the following query:

select
    json_build_object('id', i.id, 'task_id', i.task_id, 'time_spent', i.summary)
from
    intervals I
where
    extract(month from "created_at") = 10 
    and extract(year from "created_at") = 2021
group by
    i.id, i.task_id
order by i.task_id 

Which gives the following output:

json_build_object
{"id" : 53, "task_id" : 1, "time_spent" : "3373475"}
{"id" : 40, "task_id" : 1, "time_spent" : "3269108"}
{"id" : 60, "task_id" : 2, "time_spent" : "2904084"}
{"id" : 45, "task_id" : 4, "time_spent" : "1994341"}
{"id" : 38, "task_id" : 5, "time_spent" : "1933766"}
{"id" : 62, "task_id" : 5, "time_spent" : "2395378"}
{"id" : 44, "task_id" : 6, "time_spent" : "3304280"}
{"id" : 58, "task_id" : 6, "time_spent" : "3222501"}
{"id" : 48, "task_id" : 6, "time_spent" : "1990195"}
{"id" : 55, "task_id" : 7, "time_spent" : "1984300"}

How can I add subtotals of time_spent by each task?

I'd like to have an array structure of objects like this:

{
    "total": 3968600,
    "details:" [
        {"id" : 55, "task_id" : 7, "time_spent" : "1984300"}, 
        {"id" : 55, "task_id" : 7, "time_spent" : "1984300"}
    ]
}

How can I achieve it? Thank you!

CodePudding user response：

You may try the following modification which groups your data based on the task_id and uses json_agg and json_build_object to produce your desired schema.

select
    json_build_object(
        'total', SUM(i.summary),
        'details',json_agg(
            json_build_object(
                'id', i.id, 
                'task_id', i.task_id, 
                'time_spent', i.summary
             )
         )
      ) as result
from
    intervals I
where
    extract(month from "created_at") = 10 
    and extract(year from "created_at") = 2021
group by
    i.task_id
order by i.task_id 

See working demo fiddle online here