Postgres to fetch the list having comma separated values

Question

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;

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

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

Tim Biegeleisen · Accepted Answer · 2020-05-17 09:26:53Z

1

Aggregate by employee and use 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;

edited May 17, 2020 at 9:26

answered May 17, 2020 at 8:23

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jeff Cook Over a year ago

What would I need to do get all fields from both the tables ?

Tim Biegeleisen Over a year ago

Is employee.employee_id the primary key column?

Jeff Cook Over a year ago

Right, but I need all the field to be get mapped it from result set and creat object in Spring Batch

Jeff Cook Over a year ago

Yes, employee.employee_id is Primary Key column and its UUID

Tim Biegeleisen Over a year ago

@JeffCook Then you should be able to select all columns in the employee table, q.v. the updated answer. As for the other table, we would have to aggregate its columns in some way.

|

Collectives™ on Stack Overflow

Postgres to fetch the list having comma separated values

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related