PostgreSQL adding multiple results to an array

Question

Hello Stack Overflow Users,

I'm trying to pull together a list of users and all of their permissions, but I want to aggregate the permissions so they they list in the single record of the user.

The users table is pretty simple, but the permissions are broken out individually.

To give you an example, there are 3 tables: users, user_access, page

Also, I'm stuck with PostgreSQL v8.4.

The data pretty much looks like the following:

users table

user_id,username
1,bob
2,cindy
3,jen

user_access table

id,user_id,page_id,allowed
1,1,5,true
2,1,7,true
3,1,8,false
4,2,4,true
5,2,5,true
6,2,7,false
7,3,1,true
8,3,5,false

page table

page_id,page_name
1,report_1
2,report_2
3,report_3
4,admin
5,users
6,options
7,addl_options
8,advanced

So far I have been able to create an array for the permissions, but I'm only grabbing a single page permission and displaying the result of whether it's allowed or not. I'm having a hard time figuring out how to collect the rest of the pages and what they're set to within the one cell for each user.

What I have so far:

select users.user_id, users.username,
    (select array(select page.page_name || ':' || user_access.allowed)
        where users.user_id = user_access.user_id and page.page_id = user_access.page_id) as permissions
from users
    full join user_access on users.user_id = user_access.user_id
    full join page on page.page_id = user_access.page_id;

But this only returns results like the following:

user_id,username,permissions
1,bob,{report_1:<null>}
1,bob,{report_2:<null>}
1,bob,{report_3:<null>}
1,bob,{admin:<null>}
1,bob,{users:true}
1,bob,{options:<null>}
1,bob,{addl_options:true}
1,bob,{advanced:false}

What I want to get back instead is all permissions per user record like this:

user_id,username,permissions        (where permissions is an array of <page_name>:<allowed>)
1,bob,{report_1:<null>,report_2:<null>,report_3:<null>,admin:<null>,users:true,options:<null>,addl_options:true,advanced:false}
2,cindy,{report_1:<null>,report_2:<null>,report_3:<null>,admin:true,users:true,options:<null>,addl_options:false,advanced:<null>}
3,jen,{report_1:true,report_2:<null>,report_3:<null>,admin:<null>,users:false,options:<null>,addl_options:<null>,advanced:<null>}

I appreciate any advice you could assist with.

Thank you!

Answer 1

You need a cross join between users and page, then an outer join to the permissions. The result of that then needs to be grouped:

select u.user_id, array_agg(concat(p.page_name, ':', coalesce(ua.allowed::text, '<null>')) order by p.page_name)
from users u
  cross join page p
  left join user_access ua on ua.page_id = p.page_id and ua.user_id = u.user_id
group by u.user_id;  

Online example: https://rextester.com/XYC99067

(In the online example I used string_agg() instead of array_agg() as rextester doesn't display arrays nicely)