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

我正在使用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;
点赞
评论