Re: JSONB_AGG: aggregate function calls cannot be nested
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, '-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)
Re: JSONB_AGG: aggregate function calls cannot be nested
On Monday, February 22, 2021, Alexander Farber
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.
Re: JSONB_AGG: aggregate function calls cannot be nested
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, '-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, '-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
Re: JSONB_AGG: aggregate function calls cannot be nested
Alexander Farber schrieb am 20.02.2021 um 19:39:
So I am trying:
# SELECT
JSONB_AGG(TO_CHAR(finished, '-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, '-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
Re: JSONB_AGG: aggregate function calls cannot be nested
On Sat, Feb 20, 2021 at 12:34 PM Alexander Farber < alexander.far...@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.
Re: JSONB_AGG: aggregate function calls cannot be nested
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
Re: JSONB_AGG: aggregate function calls cannot be nested
On Sat, Feb 20, 2021 at 11:46 AM Alexander Farber < alexander.far...@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, '-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, '-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.
Re: JSONB_AGG: aggregate function calls cannot be nested
Then I have to split the query in 3 similar ones (with same condition)? I try: SELECT JSONB_AGG(TO_CHAR(finished, '-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, '-MM-DD')) A... ^
Re: JSONB_AGG: aggregate function calls cannot be nested
On Sat, Feb 20, 2021 at 11:39 AM Alexander Farber < alexander.far...@gmail.com> wrote: > > Or is the syntax error about being able to use JSONB_AGG only once per > SELECT query? > > That. David J.
JSONB_AGG: aggregate function calls cannot be nested
Good evening, I have the following query in 13.2: # SELECT TO_CHAR(finished, '-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, '-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
