Postgres id to name mapping in an array while creating CSV file

Question

I have a table with id to group name mapping.

1. GroupA
2. GroupB
3. GroupC
.
.
.
15 GroupO

And I have user table with userId to group ID mapping, group ID is defined as array in user table

User1 {1,5,7}
User2 {2,5,9}
User3 {3,5,11,15}
.
.
.

I want to combine to table in such a way to retrieve userID and groupName mapping in CSV file.

for example: User1 {GroupA, GroupE, GroupG}

Essentially group ID should get replace by group name while creating CSV file.

Answer 1

Setup:

create table mapping(id int, group_name text);
insert into mapping
select i, format('Group%s', chr(i+ 64))
from generate_series(1, 15) i;

create table users (user_name text, user_ids int[]);
insert into users values
('User1', '{1,5,7}'),
('User2', '{2,5,9}'),
('User3', '{3,5,11,15}');

Step by step (to understand the query, see SqlFiddle):

Use unnest() to list all single user_id in a row:

select user_name, unnest(user_ids) user_id
from users

Replace user_id with group_name by joining to mapping:

select user_name, group_name
from (
    select user_name, unnest(user_ids) id
    from users
    ) u
join mapping m on m.id = u.id

Aggregate group_name into array for user_name:

select user_name, array_agg(group_name)
from (
    select user_name, group_name
    from (
        select user_name, unnest(user_ids) id
        from users
        ) u
    join mapping m on m.id = u.id
    ) m
group by 1

Use the last query in copy command:

copy (
select user_name, array_agg(group_name)
from (
    select user_name, group_name
    from (
        select user_name, unnest(user_ids) id
        from users
        ) u
    join mapping m on m.id = u.id
    ) m
group by 1
)
to 'c:/data/example.txt' (format csv)