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!

Postgres 8.4 has been unsupported for 5 years now. You should really plan an upgrade now. — user330315
– user330315, Commented Apr 12, 2019 at 20:08
Thanks for the feedback. Part of this is being used to upgrade to a new system, which is using a supported version. But that's besides the point. — SiLeNCeD
– SiLeNCeD, Commented Apr 12, 2019 at 20:12

user330315 · Accepted Answer · 2019-04-12 20:42:50Z

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)

answered Apr 12, 2019 at 20:42

user330315

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

1 Comment

SiLeNCeD Over a year ago

Thank you @a_horse_with_no_name. That at least put me in the right direction. Unfortunately concat() is not supported until PostgreSQL v9.1, so I had to tinker with it and ended up with the following:

select u.user_id, 	(array_agg( 		coalesce(p.page_name || ': ', '') ||  coalesce(upper(ua.allowed::text), '<null>') 		)) as permissions 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;

Collectives™ on Stack Overflow

PostgreSQL adding multiple results to an array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related