Joining on array postgres

Question

Can someone please help me this, i have a blackout :)

DB postgres v 10.8

So i have two tables (Users and User_roles)

Select * from users where id = 1 

id       groups            username
1    ["read","admin"]        test

Select * from User_roles

id       name
1        write                     
2        read 
3        guest
4        admin

How can i make a join on the values in the array so my output will be:

username   user_id   user_roles_id  role
test          1          1          write
test          1          4          admin

What is the datatype of that groups column? text[], jsonb, something else? — Bergi
– Bergi, Commented Jan 10, 2022 at 11:25

arutar · Accepted Answer · 2022-01-10 11:10:53Z

1

with s as 
    (select id,
     json_array_elements_text(groups::json) as role,
     username from users
     ) 
select s.username,
     s.id user_id,
     User_roles.id user_roles_id, 
     s.role from s inner join User_roles on(s.role=User_roles.name);

output

 username | user_id | user_roles_id | role
----------+---------+---------------+-------
 test     |       1 |             4 | admin
 test     |       1 |             2 | read

edited Jan 10, 2022 at 11:10

answered Jan 10, 2022 at 8:46

arutar

1,0936 silver badges10 bronze badges

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

2 Comments

user2210516 Over a year ago

groups is a string ["read","admin"] SQL Error [42809]: ERROR: op ANY/ALL (array) requires array on right side Position: 89

arutar Over a year ago

I changed the query please check

rajorshi · Accepted Answer · 2022-01-11 00:14:53Z

SQL:

SELECT b.username,
       b.id   user_id,
       a.id   user_role_id,
       a.NAME role_name
FROM   user_roles a
       INNER JOIN (SELECT id,
                          Unnest(groups) nm,
                          username
                   FROM   users) b
               ON a.NAME = b.nm;

Output:

username | user_id | user_role_id | role_name
----------+---------+--------------+-----------
 test     |       1 |            4 | admin
 test     |       1 |            2 | read

Edit: Earlier SQL was based on the assumption that data type of 'Group' was text[]. Seems the datatype is varchar() with row inserted this way.

postgres=# insert into users values(1, '["read","admin"]','test');

So, Revised SQL:

SELECT b.username,
       b.id   user_id,
       a.id   user_role_id,
       a.NAME role_name
FROM   user_roles a
       INNER JOIN (SELECT id,
                          json_array_elements_text(groups::json) nm,
                          username
                   FROM   users) b
               ON a.NAME = b.nm;

SQL Error [42883]: ERROR: function unnest(character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 77
The SQL was based on the assumption that data type of 'Group' was text[]. Changed the SQL.

Collectives™ on Stack Overflow

Joining on array postgres

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related