mysql - Adding second join to query is making values duplicate

Question

I had an sql query that returned the user information, joining his permissions with another table that had his role permissions registered:

SELECT U.*, array_agg(RP.permission) as permission
FROM user U
JOIN role_permissions RP ON RP.role = F.role
WHERE U.email=$1
GROUP BY U.cpf -- U.cpf is the primary key

This returned the following:

{
  cpf: '11111111111',
  role: 'c-level',
  area: 'Administrativo Financeiro',
  ...,
  permission: [
    'can_edit_all_metrics',
    'can_edit_all_plans',
    'can_view_all_metrics',
    'can_generate_reports',
    ...
  ]
}

Now I'm trying to add the areas that he has access to to the query, and I'm trying it with this query:

SELECT U.*, array_agg(RP.permission) as permission, 
        array_agg(AC.has_access_to) as areas_with_access
FROM user U
JOIN role_permissions RP ON RP.role = F.role
JOIN area_access AC ON AC.area = U.area
WHERE  U.email=$1
GROUP BY U.cpf

And I'm getting this as the result:

{
  cpf: '11111111111',
  role: 'c-level',
  area: 'Administrativo Financeiro',
  ...,
  permission: [
    'can_edit_all_metrics',
    'can_edit_all_metrics',
    'can_edit_all_plans',
    'can_edit_all_plans',
    'can_view_all_metrics',
    'can_view_all_metrics',
    'can_generate_reports',
    'can_generate_reports',
    ...
  ],
  areas_with_access: [
    'Financeiro',
    'Test',
    'Financeiro',
    'Test',
    'Financeiro',
    'Test',
    'Financeiro',
    'Test',
    ...,
  ]
}

I've already tried Left/Right joining the second table, adding AC.area and AC.has_access_to to the group by clause and nothing happened. Adding RP.permission to the group by made it return only two permissions, as he only has two areas with access to.

I could just add DISTINCT to the aggregate functions but I want to know if there's something that I'm doing wrong.

This is the role permissions table:

create table if not exists role_permissions (
    role character varying(50),
    permission character varying(50),
    
    constraint pk_role_permissions primary key (role, permission),
    constraint fk_role_permissions_role foreign key (role) references roles(role) on update cascade on delete cascade,
    constraint fk_role_permissions_permission foreign key (permission) references permissions(permission) on update cascade on delete cascade,
);

This is the area access table:

create table if not exists area_access (
    area character varying(50) NOT NULL,
    has_access_to character varying(50) NOT NULL,
    
    constraint pk_area_access primary key (area, has_access_to),

    constraint fk_area foreign key (area) references area(area) on update cascade on delete cascade,
    constraint fk_has_access_to foreign key (has_access_to) references area(area) on update cascade on delete cascade,

    constraint ck_area_different CHECK (area != has_access_to)
);

Answer 1

等待大神答复