JSONB_AGG: aggregate function calls cannot be nested

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

JSONB_AGG: aggregate function calls cannot be nested

Alexander Farber
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

Reply | Threaded
Open this post in threaded view
|

Re: JSONB_AGG: aggregate function calls cannot be nested

David G Johnston
On Sat, Feb 20, 2021 at 11:39 AM Alexander Farber <[hidden email]> wrote:

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


That.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: JSONB_AGG: aggregate function calls cannot be nested

Alexander Farber
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...
                        ^

Reply | Threaded
Open this post in threaded view
|

Re: JSONB_AGG: aggregate function calls cannot be nested

David G Johnston
On Sat, Feb 20, 2021 at 11:46 AM Alexander Farber <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: JSONB_AGG: aggregate function calls cannot be nested

Alexander Farber
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

Reply | Threaded
Open this post in threaded view
|

Re: JSONB_AGG: aggregate function calls cannot be nested

David G Johnston
On Sat, Feb 20, 2021 at 12:34 PM Alexander Farber <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: JSONB_AGG: aggregate function calls cannot be nested

Thomas Kellerer-4
In reply to this post by Alexander Farber
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



Reply | Threaded
Open this post in threaded view
|

Re: JSONB_AGG: aggregate function calls cannot be nested

Alexander Farber
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


Reply | Threaded
Open this post in threaded view
|

Re: JSONB_AGG: aggregate function calls cannot be nested

David G Johnston


On Monday, February 22, 2021, Alexander Farber <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: JSONB_AGG: aggregate function calls cannot be nested

Alexander Farber
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)