Sql 使用 JSON_BUILD_OBJ 从 Postgres 返回 JSON

我有一个Postgres DB设置，并且我正在try 从查询返回特定的数据形状.

我的数据是这样的:

CREATE TABLE resource_actions(
resource TEXT NOT NULL,
actions TEXT NOT NULL,
entity TEXT NOT NULL,
UNIQUE(resource, actions, entity)
);


INSERT INTO resource_actions (resource, actions, entity) VALUES
('reading-list', 'read', 'Citizen'),
('reading-list', 'read', 'Employee'),
('books', 'read', 'Employee'),
('titles', 'update', 'Citizen'),
('titles', 'read', 'Citizen'),
('titles', 'update', 'Employee'),
('titles', 'read', 'Employee'),
('authors', 'update', 'Employee'),
('authors', 'read', 'Citizen'),
('reviews', 'read', 'Citizen'),
('reviews', 'read', 'Employee'),
('reviews', 'create', 'Citizen'),
('reviews', 'create', 'Employee'),
('employees', 'read', 'Employee'),
('employees', 'read', 'Boss'),
('employees', 'delete', 'Boss'),
('employees', 'create', 'Boss'),
('employees', 'update', 'Boss'),
('employee-schedule', 'read', 'Boss'),
('employee-schedule', 'delete', 'Boss'),
('employee-schedule', 'create', 'Boss'),
('employee-schedule', 'update', 'Boss');

我想像这样返回数据:

{
    read:['Citizen':['reading-list', 'titles', 'authors','reviews'], 'Employee':['books','reading-list', 'titles', 'authors'], 'Boss':[]]
    create:['Citizen':['reviews'], 'Employee':['reviews'], 'Boss':['employees','employee-schedule']],
    update: ['Citizen':['titles'], 'Employee':['authors','titles'], 'Boss':['employees','employee-schedule']],
    delete:['Citizen':[], 'Employee':[], 'Boss':['employees','employee-schedule']]
}

我有一个接近的SQL查询，但我无论如何也找不出需要更改哪些内容才能获得正确的数据形状.我的查询如下:

select actions, 
JSON_AGG(JSON_BUILD_OBJECT(resource, entity)) AS groupedActions
FROM resource_actions 
GROUP BY actions

我有一个SQL小提琴，显示了我到目前为止所拥有的:http://sqlfiddle.com/#!17/93bf5/1/0

这让我很接近，但我不明白如何按entity汇总数据并显示他们有权访问的资源的array.如有任何帮助，我们不胜感激！