Re: JSONB_AGG: aggregate function calls cannot be nested

2021-02-22 Thread 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, '-MM-DD') AS day, count(*) filter (where reason in ('regular', 'resigned'))

Re: JSONB_AGG: aggregate function calls cannot be nested

2021-02-22 Thread David G. Johnston
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 b

Re: JSONB_AGG: aggregate function calls cannot be nested

2021-02-22 Thread 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, '-MM-DD') AS day, count(*) filter (where reason in ('regular', 'resigned')) AS completed, count

Re: JSONB_AGG: aggregate function calls cannot be nested

2021-02-20 Thread Thomas Kellerer
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

Re: JSONB_AGG: aggregate function calls cannot be nested

2021-02-20 Thread David G. Johnston
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.

Re: JSONB_AGG: aggregate function calls cannot be nested

2021-02-20 Thread 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

Re: JSONB_AGG: aggregate function calls cannot be nested

2021-02-20 Thread David G. Johnston
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 fini

Re: JSONB_AGG: aggregate function calls cannot be nested

2021-02-20 Thread Alexander Farber
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: aggreg

Re: JSONB_AGG: aggregate function calls cannot be nested

2021-02-20 Thread David G. Johnston
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

2021-02-20 Thread Alexander Farber
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 ex