Let's say I have a table orders with the following rows:
ID Cost Date (timestamp)
1 100 2020-06-30 21:18:53.328386+00
2 45 2020-06-30 11:18:53.328386+00
3 200 2020-05-29 21:32:56.620174+00
4 20 2020-06-28 21:32:56.620174+00
And I need a query that returns exactly this:
Month Year Costs
5 2020 {"1": 0, "2": 0, ..., "29": 200, "30": 200, "31": 200}
6 2020 {"1": 0, "2": 0, ..., "28": 20, "29": 20, "30": 165}
Please note that the column Costs has to be a json with the key being the day in the month and the value being the cumulative sum of all previous days in that month.
I know this is probably not a task that postgres should be doing, but I'm just curious to see what is the solution to it (even if its not the most efficient in production environments)
You can use two levels of aggregation and json_object_agg():
select date_month, date_year, json_object_agg(date_day, cnt) costs
from (
select
extract(month from date) date_month,
extract(year from date) date_year,
extract(day from date) date_day,
sum(sum(cost)) over(
partition by extract(month from date), extract(year from date)
order by extract(day from date)
) cnt
from mytable
group by 1, 2, 3
) t
group by date_month, date_year