PostgreSQL: Join 2 tables based on an array (foreign key) column

Question

I'm using PostgreSQL and I have a table that I'm keeping my users, and in another table, I'm keeping a user's group that contains id and group's name. In the user's table, I have a column that I save each user's group id in an array. Now I want to get all data from users and the name of the groups for each user. How should I do it? here is an example: user table :

user_id  name  roles   
1        bob   [1, 2]   
2        jack  [3]

role table:

role_id  name
 1       ceo
 2       cto
 3       financial

and I expect to have :

user_id  name   role_name
 1       bob    CEO, cto
 2       jack   financial

Answer 1

As the user LJ01 said in the comments, you should join the 2 tables. If the users and groups tables have the following structure:

CREATE TABLE users (
  id BIGINT,
  name TEXT,
  group_ids BIGINT[]
);

CREATE TABLE groups (
  id BIGINT,
  name TEXT
);

You can join the tables with the following query:

SELECT u.*,g.name FROM users u JOIN groups g ON g.id = ANY (u.group_ids);

So if the users table has the following data:

id  name    group_ids
1   Test1   {1,2,3}
2   Test2   {3,4}

And there are 4 groups:

id  name
1   Group1
2   Group2
3   Group3
4   Group4

The result of the query will be

1   Test1   {1,2,3} Group1
1   Test1   {1,2,3} Group2
1   Test1   {1,2,3} Group3
2   Test2   {3,4}   Group3
2   Test2   {3,4}   Group4

UPDATE The user asked for one row per user with the groups aggregated in a single row. That can be achieved with the following query:

  SELECT u.id, u.name,array_agg(g.name) group_names FROM users u JOIN groups g ON g.id = ANY (u.group_ids)
  GROUP BY u.id, u.name;

If we execute this query on the example data that is shown on the begining of the answer, we are going to get the following results:

u.id u.name  group_names
1    Test1   {Group1,Group2,Group3}
2    Test2   {Group3,Group4}