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

0
votes

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

Table:

CREATE TABLE invoices (
    id serial NOT NULL PRIMARY KEY,
    data jsonb NOT NULL
);

What I try to achieve sum(values) by month:

id date total amount
1 2021-01 65
2 2020-12 14

I know that postgres offers:

  • extract month
  • date_trunc

It seems not to work with jsonb.

1
jsonpostgresqljsonb

1 Answers

1
votes

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')