[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: JSONB_AGG: aggregate function calls cannot be nested
From:       Alexander Farber <alexander.farber () gmail ! com>
Date:       2021-02-22 20:53:14
Message-ID: CAADeyWjd8WVOqHXWqyG0KdaY1gPku=cr5t15pzBUj55x3pMEsQ () mail ! gmail ! com
[Download RAW message or body]

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)


[Attachment #3 (text/html)]

<div dir="ltr">Ahh, thank you all -<br><br> select row_to_json (x) FROM( SELECT <br>  \
jsonb_agg(day) AS day,<br>        jsonb_agg(completed) AS completed,<br>        \
jsonb_agg(expired) AS expired<br>from (<br>        SELECT TO_CHAR(finished, \
&#39;YYYY-MM-DD&#39;) AS day,<br>        count(*) filter (where reason in \
(&#39;regular&#39;, &#39;resigned&#39;)) AS completed,<br>        count(*) filter \
(where reason = &#39;expired&#39;) AS expired<br>        FROM words_games<br>        \
WHERE finished &gt; CURRENT_TIMESTAMP - INTERVAL &#39;2 week&#39;<br>        GROUP BY \
day<br>) t<br>ORDER BY day) x;<br>                                                    \
<br>                                row_to_json                                       \
<br>                                                                            \
<br>---------------------------------------------------------------------------------- \
----------------------------------------------------------------------------<br>------ \
-------------------------------------------------------------------------------------- \
------------------------------------------------------------------<br>----------------------------------------------------------------------------<br> \
{&quot;day&quot;:[&quot;2021-02-16&quot;, &quot;2021-02-20&quot;, \
&quot;2021-02-10&quot;, &quot;2021-02-09&quot;, &quot;2021-02-15&quot;, \
&quot;2021-02-19&quot;, &quot;2021-02-17&quot;, &quot;2021-02-11&quot;, \
&quot;2021-02-22&quot;, &quot;2021-02-08&quot;, &quot;2021-02-<br>14&quot;, \
&quot;2021-02-21&quot;, &quot;2021-02-12&quot;, &quot;2021-02-13&quot;, \
&quot;2021-02-18&quot;],&quot;completed&quot;:[744, 802, 864, 770, 767, 745, 837, \
792, 751, 32, 843, 808, 838, 853, 751],&quot;expired&quot;:<br>[237, 168, 230, 263, \
203, 257, 206, 184, 337, 11, 231, 380, 231, 293, 196]}<br>(1 row)<br></div>



[prev in list] [next in list] [prev in thread] [next in thread]