Postgres how to sum (aggregate) all the values in a key/value json?-CodePudding

My query is here:

select datetime,array_to_json(array_agg(json_build_object('parameter',parameter,'channel_id',channel_id,'value',value,'status',status,'units',units)))  as parameters
from dbp_istasyondata 
where site_id=10 
  and channel_id IN (0,1,2,3,4) 
  and datetime between '2022-12-01T00:00:00' and '2022-12-01T01:30:00'
group by 1
order by 1;

This is how it came out as a response to this query:

"datetime"  "parameters"
"2022-12-01 00:00:00"   "[{""channel_id"" : 0, ""value"" : 7.72},{""channel_id"" : 1, ""value"" : 1593.87}]"
"2022-12-01 00:01:00"   "[{""channel_id"" : 1, ""value"" : 1612.26},{""channel_id"" : 0, ""value"" : 7.72}]"
"2022-12-01 00:02:00"   "[{""channel_id"" : 0, ""value"" : 7.72},{""channel_id"" : 1, ""value"" : 1615.36}]"
"2022-12-01 00:03:00"   "[{""channel_id"" : 0, ""value"" : 7.72},{""channel_id"" : 1, ""value"" : 1625.99}]"
"2022-12-01 00:04:00"   "[{""channel_id"" : 0, ""value"" : 7.71},{""channel_id"" : 1, ""value"" : 1623.12}]"
"2022-12-01 00:05:00"   "[{""channel_id"" : 0, ""value"" : 7.72},{""channel_id"" : 1, ""value"" : 1638.58}]"
"2022-12-01 01:00:00"   "[{""channel_id"" : 0, ""value"" : 7.74},{""channel_id"" : 1, ""value"" : 1647.09}]"
"2022-12-01 01:01:00"   "[{""channel_id"" : 0, ""value"" : 7.74},{""channel_id"" : 1, ""value"" : 1656.71}]"
"2022-12-01 01:02:00"   "[{""channel_id"" : 1, ""value"" : 1646.86},{""channel_id"" : 0, ""value"" : 7.74}]"
"2022-12-01 01:03:00"   "[{""channel_id"" : 1, ""value"" : 1656.34},{""channel_id"" : 0, ""value"" : 7.74}]"
"2022-12-01 01:04:00"   "[{""channel_id"" : 1, ""value"" : 1652.63},{""channel_id"" : 0, ""value"" : 7.74}]"
"2022-12-01 01:05:00"   "[{""channel_id"" : 0, ""value"" : 7.74},{""channel_id"" : 1, ""value"" : 1648.01}]"

The result I want:

"datetime"  "parameters"
"2022-12-01 00:00:00"   "[{""channel_id"" : 0, ""value"" : 50},{""channel_id"" : 1, ""value"" : 1593.87}]"
"2022-12-01 01:00:00"   "[{""channel_id"" : 1, ""value"" : 102348},{""channel_id"" : 0, ""value"" : 7.72}]"

The result I want is just an hourly collection of values and display as json. Is there a way to this?

As a result of my research, I found such a sum, but I could not get it to give me the answer I wanted. Can you help me ?

select date_trunc('hour', datetime), SUM (value) as total 
from dbp_istasyondata 
where channel_id=3 
  and site_id=16 
  and datetime between '2022-11-01T00:00:00' and '2022-12-01T04:00:00' 
group by 1;

CodePudding user response：

As I understand your question, you want a resultset with one row per hour, and a column holding a JSON array that gives the sum of values of each channel.

You would typically need two levels of aggregation ; one to compute the aggregates per hour and per channel, and then another to aggregate per hour only.

select datehour, jsonb_agg( to_jsonb(t) - 'datehour') res 
from (
    select date_trunc('hour', datetime) datehour, channel_id, sum(value) value
    from bp_istasyondata 
    where site_id = 10 
        and channel_id in (0, 1, 2, 3, 4) 
        and datetime between '2022-12-01T00:00:00' and '2022-12-01T01:30:00'
    group by 1, 2
) t
group by 1

Notes:

date_trunc truncates a timestamp or date to a given precision
to_jsonb(t) converts each recordset returned by the subquery to a JSONB object (from which we remove the datehour key using jsonb operator -)
aggregate function json_agg gathers all objects into a JSONB array