Good evening,
I have the following query in 13.2:
# SELECT
TO_CHAR(finished, 'YYYY-MM-DD') AS day,
SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1 ELSE 0 END)::int AS completed,
SUM(CASE WHEN reason='expired' THEN 1 ELSE 0 END)::int AS expired
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
ORDER BY day;
day | completed | expired
------------+-----------+---------
2021-02-06 | 167 | 71
2021-02-07 | 821 | 189
2021-02-08 | 816 | 323
2021-02-09 | 770 | 263
2021-02-10 | 864 | 230
2021-02-11 | 792 | 184
2021-02-12 | 838 | 231
2021-02-13 | 853 | 293
2021-02-14 | 843 | 231
2021-02-15 | 767 | 203
2021-02-16 | 744 | 237
2021-02-17 | 837 | 206
2021-02-18 | 751 | 196
2021-02-19 | 745 | 257
2021-02-20 | 654 | 135
(15 rows)
It works well, but I would like to transform it into a JSONB map with 3 arrays.
So I am trying:
So I am trying:
# SELECT
JSONB_AGG(TO_CHAR(finished, 'YYYY-MM-DD')) AS day,
JSONB_AGG(SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1 ELSE 0 END)::int) AS completed,
JSONB_AGG(SUM(CASE WHEN reason='expired' THEN 1 ELSE 0 END)::int) AS expired
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
ORDER BY day;
ERROR: aggregate function calls cannot be nested
LINE 3: JSONB_AGG(SUM(CASE WHEN reason='regular' or ...
^
Shouldn't I use JSONB_AGG here, to build the 3 JSON arrays?
Or is the syntax error about being able to use JSONB_AGG only once per SELECT query?
Greetings
Or is the syntax error about being able to use JSONB_AGG only once per SELECT query?
Greetings
Alex
