Postgres获取具有逗号分隔值的列表 [英] Postgres to fetch the list having comma separated values

问题描述

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

I am working on Postgres SQL and having below join query, when I execute this query I get two or 3 records for each employee, because each employee has 3 different types of email address 1) Home Address 2) Office address 3) social address.

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;

我想要的是employee_email.email列，以在输出中提供逗号分隔的值，我们该怎么做?

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

注意:我在数据库中有近一百万条记录，并将使用Spring Batch将数据从Postgres迁移到MongoDB.我需要为Phone做同样的事情

Note: I've almost 1 million records in DB and will use Spring Batch to migrate data from Postgres to MongoDB. The same I would need to do for Phone

推荐答案

按员工汇总并使用string_agg:

select
    c.employee_id,         -- or just c.* assuming employee_id is a PK
    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;

这篇关于Postgres获取具有逗号分隔值的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！