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)

Answer 2

Say you have two tables in this form:

Table groups
 Column   |  Type   
-----------+---------
 groupname | text     
 groupid   | integer  

Table users
 Column  |   Type    
----------+----------
 username | text     
 groupids | integer[]  <-- group ids as inserted in table groups

You can query the users replacing the group id with group names with this code:

WITH users_subquery AS (select username,unnest (groupids) AS groupid FROM users) 
SELECT username,array_agg(groupname) AS groups 
FROM users_subquery JOIN groups ON users_subquery.groupid = groups.groupid 
GROUP BY username

If you need the groups as string (useful for the csv export), surround the query with a array_to_string statement:

SELECT username, array_to_string(groups,',') FROM 
(
  WITH users_subquery AS (select username,unnest (groupids) AS groupid FROM users) 
  SELECT username,array_agg(groupname) AS groups 
  FROM users_subquery JOIN groups ON users_subquery.groupid = groups.groupid 
  GROUP BY username
) as foo;

Result:

 username |    groups    
----------+-----------------
 user1    | group1,group2
 user2    | group2,group3

Answer 3

We can use unnest directly in the from clause as it has implicit lateral join (tested in postgres 15)

select user_name, array_agg(group_name)
from users, unnest(user_ids) as user_id, mapping m
where m.id=user_id
group by 1;

To retain user rows who don't belong to any group, you can use below query

select user_name, array_agg(group_name)
from users
    left join lateral unnest(user_ids) as user_id on true
    left join mapping m on m.id=user_id
group by 1;

all the queries are here dbfiddle aOvASjoq