Postgresql我怎么能总和(值)和组他们在jsonb的月份?

Question

Postgres Query:

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

输出:

<表类>id日期总量tbody><<tr>12021-01-161322021-01-125232020-12-171142020-12-083

您需要将文本值转换为date，然后您可以例如使用to_char()从中获取月份:

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