postgresql - 将列的jsonb行合并到postgres中的json构建对象中

Question

共有三列id : integer auto increment , col_jsonb: jsonb , date: timestamp。我想col_jsonb基于date所需的输出 将行值合并到 json 构建对象中

桌子：

+----+----------------+------------+
| 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 |
+----+----------------+------------+

预期的o / p：

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

标签： postgresqljsonb

Answer 1

试试这个查询：

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

row_number如果需要，为行编号添加

演示