json - Postgresql how can I sum(values) and group them by the month within jsonb?

Question

Postgres Query:

SELECT
  ((data->>'date')) AS time,
  ((data->>'totalAmount')) as values
FROM invoices

Output:

id	date	total amount
1	2021-01-16	13
2	2021-01-12	52
3	2020-12-17	11
4	2020-12-08	3

question from:https://stackoverflow.com/questions/65890855/postgresql-how-can-i-sumvalues-and-group-them-by-the-month-within-jsonb

Answer 1

You need to cast the text value to a date then you can e.g. use to_char() to get just the month from it:

select to_char((data->>'date')::date, 'yyyy-mm') AS month,
       sum((data->>'totalAmount')::int) as values
from invoices
group by to_char((data->>'date')::date, 'yyyy-mm')