时间戳

首页 > 解决方案 > PostgreSQL 从现有数据创建 JSON 列

json - PostgreSQL 从现有数据创建 JSON 列

问题描述

给定如下数据：

+---+------------+------------
|id | change     | date       
+---+------------+------------
| 1 | name       | 2018-06-20
| 2 | address    | 2018-06-20
| 3 | email      | 2018-06-20
| 4 | email      | 2018-06-21
| 5 | address    | 2018-06-22
| 6 | address    | 2018-06-23

我正在尝试创建一个视图，将上述内容汇总到一个包含以下数据的 json 列中：

{"name":["2018-06-20"], "address":["2018-06-20","2018-06-22","2018-06-23"], "email":["2018-06-20","2018-06-21"]}

我一直在尝试使用 array_aggr、array_to_json、json_agg、array_build_object 函数来解决这个问题，但我似乎无法完全正确。

我希望有人能帮帮忙。

干杯

标签： jsonpostgresqljsonb

解决方案

您应该为两个级别使用两次 jsonb 聚合：

select jsonb_pretty(jsonb_object_agg(change, dates))
from (
    select change, jsonb_agg(date) as dates
    from my_table
    group by change
    ) s

     jsonb_pretty      
-----------------------
 {                    +
     "name": [        +
         "2018-06-20" +
     ],               +
     "email": [       +
         "2018-06-20",+
         "2018-06-21" +
     ],               +
     "address": [     +
         "2018-06-20",+
         "2018-06-22",+
         "2018-06-23" +
     ]                +
 }
(1 row)

请注意，这jsonb_pretty()是不必要的，仅用于良好的输出。

推荐阅读