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

Please Edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. edit your question - do not post code or additional information in comments. — user330315
– user330315, Commented Jun 9, 2018 at 10:58

Dimitar Spasovski · Accepted Answer · 2018-06-10 10:11:13Z

11

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}

edited Jun 10, 2018 at 10:11

answered Jun 9, 2018 at 9:01

Dimitar Spasovski

2,18210 gold badges29 silver badges48 bronze badges

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

2 Comments

fariba.j Over a year ago

thanks it's working but it returns all rows that have group_ids. how can the rows which have group_ids = null?

Dimitar Spasovski Over a year ago

If you want to return the users with group_ids = null, then replace the JOIN part with LEFT JOIN

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related