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

Question

Refer here for the table

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 Refer here for the 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 |
+----+----------------------------------------------+------------+

Why does have {"Morning":20} the id 2? In the input data id 2 is associated with {"Evening":20}. — user330315
– user330315, Commented Aug 10, 2020 at 8:28

Akhilesh Mishra · Accepted Answer · 2020-08-10 08:26:51Z

2

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

answered Aug 10, 2020 at 8:26

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related