ALTER TABLE blc_admin_user ADD permission_ids VARCHAR(255);
ALTER TABLE blc_admin_user ADD role_ids VARCHAR(255);
with role_table as
(
select admin_user_id, json_agg(admin_role_id) as roles_json
from
blc_admin_user_admin_role_xref
group by admin_user_id
)
update
blc_admin_user
admin_user_table
set role_ids = role_table.roles_json
from
role_table
where
admin_user_table.id = role_table.admin_user_id;
with permission_table as
(
select admin_user_id, json_agg(admin_permission_id) as
perms_json
from blc_admin_user_admin_permission_xref
group by admin_user_id
)
update
blc_admin_user admin_user_table
set permission_ids =
permission_table.perms_json
from
permission_table
where admin_user_table.id =
permission_table.admin_user_id;