select domain, json_agg(parts) as permissions from
            (select domain, row_to_json(r) as parts from
                    (select domain, action, array_agg(distinct target) as target from
                        (select (case when domain is null then '*' else domain end) as domain,
                                (case when target is null then '*' else target end) as target,
                                array_agg(distinct (case when action is null then '*' else action end)) as action
                           from permission
                          group by domain, target
                         ) x
                      group by domain, action)
              r) parts
        group by domain;

def _get_permissions_query(self, session, identifier):
        """
        select domain, json_agg(parts) as permissions from
            (select domain, row_to_json(r) as parts from
                    (select domain, action, array_agg(distinct target) as target from
                        (select (case when domain is null then '*' else domain end) as domain,
                                (case when target is null then '*' else target end) as target,
                                array_agg(distinct (case when action is null then '*' else action end)) as action
                           from permission
                          group by domain, target
                         ) x
                      group by domain, action)
              r) parts
        group by domain;
        """
        thedomain = case([(Domain.name == None, '*')], else_=Domain.name)
        theaction = case([(Action.name == None, '*')], else_=Action.name)
        theresource = case([(Resource.name == None, '*')], else_=Resource.name)

        action_agg = func.array_agg(theaction.distinct())

        stmt1 = (
            session.query(Permission.domain_id,
                          thedomain.label('domain'),
                          Permission.resource_id,
                          theresource.label('resource'),
                          action_agg.label('action')).
            select_from(User).
            join(role_membership_table, User.pk_id == role_membership_table.c.user_id).
            join(role_permission_table, role_membership_table.c.role_id == role_permission_table.c.role_id).
            join(Permission, role_permission_table.c.permission_id == Permission.pk_id).
            outerjoin(Domain, Permission.domain_id == Domain.pk_id).
            outerjoin(Action, Permission.action_id == Action.pk_id).
            outerjoin(Resource, Permission.resource_id == Resource.pk_id).
            filter(User.identifier == identifier).
            group_by(Permission.domain_id, Domain.name, Permission.resource_id, Resource.name)).subquery()

        stmt2 = (session.query(stmt1.c.domain,
                               stmt1.c.action,
                               func.array_agg(stmt1.c.resource.distinct()).label('resource')).
                 select_from(stmt1).
                 group_by(stmt1.c.domain, stmt1.c.action)).subquery()

        stmt3 = (session.query(stmt2.c.domain,
                               func.row_to_json(as_row(stmt2)).label('parts')).
                 select_from(stmt2)).subquery()

        final = (session.query(stmt3.c.domain, cast(func.json_agg(stmt3.c.parts), Text)).
                 select_from(stmt3).
                 group_by(stmt3.c.domain))

        return final