JSONB_AGG: aggregate function calls cannot be nested

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: JSONB_AGG: aggregate function calls cannot be nested
Date: 2021-02-20 18:39:05
Message-ID: CAADeyWhzYAXxuBJaQA2n-wuK1Uoc2FbKAk+iE2G1Mcg-vD0r0g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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:

# 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
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2021-02-20 18:43:25 Re: JSONB_AGG: aggregate function calls cannot be nested
Previous Message Alexander Farber 2021-02-20 15:42:26 Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached