Re: JSONB_AGG: aggregate function calls cannot be nested

[Alexander Farber <alexander.farber@xxxxxxxxx>]

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)