Joining on array postgres-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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