Search Postgresql Archives

Re: JSONB_AGG: aggregate function calls cannot be nested

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux