JSONB_AGG: aggregate function calls cannot be nested

On Sat, Feb 20, 2021 at 11:39 AM Alexander Farber <
alexander.farber@gmail.com> wrote:

Or is the syntax error about being able to use JSONB_AGG only once per
SELECT query?

That.

David J.

Then I have to split the query in 3 similar ones (with same condition)?

I try:

SELECT
JSONB_AGG(TO_CHAR(finished, 'YYYY-MM-DD')) AS day
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
ORDER BY day;
ERROR: aggregate functions are not allowed in GROUP BY
LINE 2: JSONB_AGG(TO_CHAR(finished, 'YYYY-MM-DD')) A...
^

On Sat, Feb 20, 2021 at 11:46 AM Alexander Farber <
alexander.farber@gmail.com> wrote:

Then I have to split the query in 3 similar ones (with same condition)?

I try:

SELECT
JSONB_AGG(TO_CHAR(finished, 'YYYY-MM-DD')) AS day
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
ORDER BY day;
ERROR: aggregate functions are not allowed in GROUP BY
LINE 2: JSONB_AGG(TO_CHAR(finished, 'YYYY-MM-DD')) A...
^

That's a whole different misunderstanding of aggregates that you are seeing.

I mis-spoke in the prior response though. Its not that you only get one
column of an aggregate function per select - you only get to use a single
aggregate in each expression in a select/group-by. array_agg(sum(...)) is
two aggregates in a single expression.

David J.

Ah, thank you...

JSON support in PostgreSQL is cool and seems to be extended with each
release.

But standard tasks of returning a JSON map of lists or JSON list of list
seem to be difficult to use.

Greetings
Alex

On Sat, Feb 20, 2021 at 12:34 PM Alexander Farber <
alexander.farber@gmail.com> wrote:

Ah, thank you...

JSON support in PostgreSQL is cool and seems to be extended with each
release.

But standard tasks of returning a JSON map of lists or JSON list of list
seem to be difficult to use.

With experience it just becomes verbose - at least for non-trivial cases.

David J.

Alexander Farber schrieb am 20.02.2021 um 19:39:

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 ...

You need a second level of grouping:

select day as day,
jsonb_agg(completed) as completed,
jsonb_agg(expired) as expired)
from (
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
) t
GROUP BY day
ORDER BY day;

Btw:

SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1 ELSE 0 END)::int AS completed,

can also be written as

count(*) filter (where reason in ('regular', 'resigned') as completed

Thank you Thomas, this results in

select
day AS day,
jsonb_agg(completed) AS completed,
jsonb_agg(expired) AS expired
from (
SELECT TO_CHAR(finished, 'YYYY-MM-DD') AS day,
count(*) filter (where reason in ('regular', 'resigned')) AS
completed,
count(*) filter (where reason = 'expired') AS expired
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
) t
GROUP BY day
ORDER BY day;
day | completed | expired
------------+-----------+---------
2021-02-08 | [481] | [155]
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 | [802] | [168]
2021-02-21 | [808] | [380]
2021-02-22 | [402] | [255]
(15 rows)

but how to get a JSON map of lists here? I am trying:

select row_to_json (x) FROM (SELECT
day AS day,
jsonb_agg(completed) AS completed,
jsonb_agg(expired) AS expired
from (
SELECT TO_CHAR(finished, 'YYYY-MM-DD') AS day,
count(*) filter (where reason in ('regular', 'resigned')) AS
completed,
count(*) filter (where reason = 'expired') AS expired
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
) t
GROUP BY day
ORDER BY day) x;
row_to_json
--------------------------------------------------------
{"day":"2021-02-08","completed":[475],"expired":[155]}
{"day":"2021-02-09","completed":[770],"expired":[263]}
{"day":"2021-02-10","completed":[864],"expired":[230]}
{"day":"2021-02-11","completed":[792],"expired":[184]}
{"day":"2021-02-12","completed":[838],"expired":[231]}
{"day":"2021-02-13","completed":[853],"expired":[293]}
{"day":"2021-02-14","completed":[843],"expired":[231]}
{"day":"2021-02-15","completed":[767],"expired":[203]}
{"day":"2021-02-16","completed":[744],"expired":[237]}
{"day":"2021-02-17","completed":[837],"expired":[206]}
{"day":"2021-02-18","completed":[751],"expired":[196]}
{"day":"2021-02-19","completed":[745],"expired":[257]}
{"day":"2021-02-20","completed":[802],"expired":[168]}
{"day":"2021-02-21","completed":[808],"expired":[380]}
{"day":"2021-02-22","completed":[410],"expired":[255]}
(15 rows)

While I would actually need:

{
"day": [ "2021-02-08", "2021-02-09", ... ],
"completed": [ 475, 770, ...],
"expired": [ 155, 263 , ...]
}

And then I could feed the data into the Chart.js shown at the bottom of my
web page https://slova.de/top

Currently I do a simple SELECT query and construct the JSON map of list in
the Java code of my servlet

Thank you
Alex

On Monday, February 22, 2021, Alexander Farber <alexander.farber@gmail.com>
wrote:

but how to get a JSON map of lists here? I am trying:

{
"day": [ "2021-02-08", "2021-02-09", ... ],
"completed": [ 475, 770, ...],
"expired": [ 155, 263 , ...]
}

If you want the days aggregated then don’t “group by day”

David J.

Ahh, thank you all -

select row_to_json (x) FROM( SELECT
jsonb_agg(day) AS day,
jsonb_agg(completed) AS completed,
jsonb_agg(expired) AS expired
from (
SELECT TO_CHAR(finished, 'YYYY-MM-DD') AS day,
count(*) filter (where reason in ('regular', 'resigned')) AS
completed,
count(*) filter (where reason = 'expired') AS expired
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
) t
ORDER BY day) x;

row_to_json

--------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------
{"day":["2021-02-16", "2021-02-20", "2021-02-10", "2021-02-09",
"2021-02-15", "2021-02-19", "2021-02-17", "2021-02-11", "2021-02-22",
"2021-02-08", "2021-02-
14", "2021-02-21", "2021-02-12", "2021-02-13",
"2021-02-18"],"completed":[744, 802, 864, 770, 767, 745, 837, 792, 751, 32,
843, 808, 838, 853, 751],"expired":
[237, 168, 230, 263, 203, 257, 206, 184, 337, 11, 231, 380, 231, 293, 196]}
(1 row)