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
