PostgreSQL 将多个结果添加到数组中

Question

你好堆栈溢出用户，

我正在尝试将用户及其所有权限的列表放在一起，但我想聚合权限，以便它们列在用户的单个记录中。

用户表非常简单，但权限是单独划分的。

举个例子，有3个表：用户，user_access，页面

另外，我坚持使用PostgreSQL v8.4。

数据大致如下所示：

用户表

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

user_access表

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_id,page_name
1,report_1
2,report_2
3,report_3
4,admin
5,users
6,options
7,addl_options
8,advanced

到目前为止，我已经能够为权限创建一个数组，但我只抓取一个页面权限并显示是否允许的结果。我很难弄清楚如何收集其余页面以及它们在每个用户的一个单元格中的设置。

到目前为止，我拥有的：

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;

但这仅返回如下结果：

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}

我想取回的是每个用户记录的所有权限，如下所示：

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>}

感谢您提供的任何建议。

谢谢！

Answer 1

您需要在用户和页面之间进行交叉联接，然后对权限进行外部联接。然后需要对结果进行分组：

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;  

在线示例：https://rextester.com/XYC99067

(在在线示例中，我使用string_agg()而不是array_agg()，因为 rextester 不能很好地显示数组)