Postgres JSON Laravel 8中的计数对象聚合

Question

我在表的每一行都有这个JSON字段，记录风险管理工具中谁参与了安全事件，以及他们在该事件中的角色。

'roles':[
{
"user":
{
"user_id": "c4456c9c-880b-4d1f-8066-47d77e2b6eac",
"name": "James Dean",
},
"role": "170"
},
{
"user":
{
"user_id": "cd91f708-12cd-4541-9022-6gf6a44a2e43",
"name": "John Smith",
},
"role": "172"
}
]

我需要用户对角色的总结。角色数量未知，尽管通常在2-6个角色之间。这份报告的最终结果看起来像：

例如

角色171[/tr>詹姆斯·迪恩约翰·史密斯

用户		角色170	角色172

我为您编写了示例：

CREATE TABLE testdata (
jsondata json NULL
);
INSERT INTO testdata (jsondata) VALUES('{
"roles": [
{
"user": {
"user_id": "c4456c9c-880b-4d1f-8066-47d77e2b6eac",
"name": "James Dean"
},
"role": "170"
},
{
"user": {
"user_id": "cd91f708-12cd-4541-9022-6gf6a44a2e43",
"name": "John Smith"
},
"role": "172"
}
]
}
'::json);

如果预先知道滚动列的数量，则使用以下查询：

select 
t1.names, 
case when t1.roles = '170' then 1 else 0 end as "Role 170", 
case when t1.roles = '171' then 1 else 0 end as "Role 171", 
case when t1.roles = '172' then 1 else 0 end as "Role 172" 
from (
select 
json_array_elements((jsondata->>'roles')::json)->>'role' as roles, 
((json_array_elements((jsondata->>'roles')::json)->>'user')::json)->>'name' as names
from testdata
) t1 
Result: 
names       Role 170    Role 171    Role 172
James Dean  1           0           0
John Smith  0           0           1

如果滚动列的数量事先不知道，那么可以使用postgress透视表(交叉表(函数。在使用交叉表函数之前，必须在postgres扩展tablefunc上安装