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.