Postgres获取具有逗号分隔值的列表

由 发布于 postgresqlstring-aggregation
 收藏

我正在使用Postgres SQL，并具有下面的联接查询，当我执行此查询时，我会为每个员工获得两条或3条记录，因为每位员工都有3种不同类型的电子邮件地址1）家庭地址2）办公地址3）社交地址。

select * from root.employee c
full outer join root.employee_email ce
on c.employee_id = ce.employee_id
order by c.employee_id limit 1000 offset 0;

What I want is that employee_email.email column to give the comma separated value in the output, how can we do that ?

注意：我在数据库中有近10个lacs记录（一百万个），并将使用Spring Batch将数据从Postgres迁移到MongoDB。我需要为Phone做同样的事情

评论
nvelit
nvelit

Aggregate by employee and use string_agg:

select
    c.employee_id,
    string_agg(ce.email, ',') as emails
from root.employee c
full outer join root.employee_email ce
    on c.employee_id = ce.employee_id
group by
    c.employee_id
order by
    c.employee_id
limit 1000
offset 0;
点赞
评论