Convert Django JSON field to Text in subquery

I have two models Assignment and Application. Assignment has meta field which has uuid data that is used to match uuid field on Application. My goal is to join assignee information when I query applications.

I have some code like this using subquery.

assignment_subquery = Assignment.objects.filter(
    meta__uuid=Cast(OuterRef('uuid'), TextField())
).values('assignee__username')[:1]

# Query applications and annotate them with the assignee's username
applications_with_assignee = Application.objects.annotate(
    assignee_username=Subquery(assignment_subquery)
)

It produce SQL like this

SELECT "application"."id",
       "application"."uuid",
       (SELECT U1."username"
        FROM "assignment" U0
                 INNER JOIN "auth_user" U1 ON (U0."assignee_id" = U1."id")
        WHERE (U0."meta" -> 'uuid') = ("application"."uuid")::text
        LIMIT 1) AS "assignee_username"
FROM "application";

It is almost correct except U0."meta" -> 'uuid' instead of U0."meta" ->> 'uuid', which I believe extracts the value associated with the specified key as text. I can't figure out how to get it to generate the right query.

Thank you very much for helping.

Вернуться на верх