Is there a way to dynamically annotate value from JSONField using another annotation in Django?

What I have is a following annotation:

member_qs = RoomMember.objects.filter(room_id=OuterRef('id'), ...)
qs = Room.objects.annotate(
    person_role=Case(
        When(Exists(member_qs), then=Subquery(member_qs.values_list('role', flat=True))),
        default=Value(request.user.global_role)
    ),
    person_permissions=Case(
        When(Q(person_role='creator'), then=F('role_permissions__creator')),
        When(Q(person_role='moderator'), then=F('role_permissions__moderator')),
        When(Q(person_role='member'), then=F('role_permissions__member')),
        When(Q(person_role='authenticated'), then=F('role_permissions__authenticated')),
        default=F('role_permissions__anonymous')
    ),
    can_chat=F('person_permissions__chat_send'),
    can_add_videos=F('person_permissions__queue_extend'),
)

... where role_permissions is a JSONField in this form: {"role_name": {"permission_name": true|false}}

This works but results in an ugly and very slow SQL. What I want is to simplify annotation of person_permissions or get rid of it completely, but I can't use person_role directly because F is hard-coded.

Back to Top