使用Postgres的JSON_BUILD_OBJ构造指定结构的JSON查询结果

需求说明 #

从resource_actions表查询数据，生成以操作（actions）为顶级键，每个操作下按角色（entity）聚合对应资源数组的JSON结构。

表结构与测试数据 #

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", "employees"],
        "Boss": ["employees", "employee-schedule"]
    },
    "create": {
        "Citizen": ["reviews"],
        "Employee": ["reviews"],
        "Boss": ["employees", "employee-schedule"]
    },
    "update": {
        "Citizen": ["titles"],
        "Employee": ["authors", "titles"],
        "Boss": ["employees", "employee-schedule"]
    },
    "delete": {
        "Boss": ["employees", "employee-schedule"]
    }
}

注：原期望中read的Boss数组为空是笔误，实际数据中Boss拥有employees和employee-schedule的read权限

调整后的SQL查询 #

原语句仅按actions分组，无法实现按entity聚合资源数组，需通过三层分组嵌套完成层级聚合：

SELECT json_object_agg(actions, entity_resource_map) AS final_result
FROM (
    -- 第二层：按操作分组，聚合角色与对应资源数组的映射
    SELECT 
        actions,
        json_object_agg(entity, resource_list) AS entity_resource_map
    FROM (
        -- 第一层：按操作+角色分组，聚合该角色拥有的资源数组
        SELECT 
            actions,
            entity,
            json_agg(resource ORDER BY resource) AS resource_list
        FROM resource_actions
        GROUP BY actions, entity
    ) AS role_resource_groups
    GROUP BY actions
) AS action_role_groups;

语句说明 #

1. 最内层子查询：按actions和entity分组，用json_agg聚合每个角色对应所有资源，生成有序的资源数组
2. 中间层子查询：按actions分组，用json_object_agg将每个角色与对应的资源数组构造成键值对对象
3. 最外层查询：用json_object_agg将每个操作作为顶级键，对应的角色-资源映射对象作为值，生成最终的JSON结构

内容的提问来源于stack exchange，提问作者foo_bar_zing