Merge jsonb rows of a column into json build object in postgres

Question

There are three columns id : integer auto increment , col_jsonb: jsonb , date: timestamp. I want to merge col_jsonb row values into json build object based on date, the required output

Table:

+----+----------------+------------+
| id |   col_jsonb    |    date    |
+----+----------------+------------+
|  1 | {"Morning":10} | 2020-08-09 |
|  2 | {"Evening":20} | 2020-08-09 |
|  3 | {"Night":30}   | 2020-08-09 |
|  4 | {"Morning":20} | 2020-08-10 |
+----+----------------+------------+

Expected o/p:

+----+----------------------------------------------+------------+
| id |                  col_jsonb                   |    date    |
+----+----------------------------------------------+------------+
|  1 | [{"Morning":10},{"Evening":20},{"Night":30}] | 2020-08-09 |
|  2 | {"Morning":20}                               | 2020-08-10 |
+----+----------------------------------------------+------------+

Answer 1

Try This query:

select 
    row_number() over (order by date_) as "id", 
    jsonb_agg(col_jsonb), 
    date_ as "Date"
from 
    example 
group by 
    date_

row_number is added for numbering of rows if required

DEMO