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'))
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
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
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
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.
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
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
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
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.
