如何在Django子查询中将JSON字段转为文本进行关联查询

问题分析 #

你当前的查询生成的SQL中，meta -> 'uuid'返回的是JSON类型值，和文本格式的UUID做等值匹配会出现逻辑问题，必须改成meta ->> 'uuid'（提取文本格式的JSON值）才能正确匹配。

解决方案：使用KeyTextTransform #

Django内置的KeyTextTransform函数专门用于从JSON字段中提取文本格式的值，对应SQL的->>操作符。修改你的子查询代码如下：

from django.db.models import OuterRef, Subquery, Cast, TextField
from django.db.models.functions import KeyTextTransform

# 构建子查询，用KeyTextTransform提取meta中uuid的文本值
assignment_subquery = Assignment.objects.filter(
    KeyTextTransform('uuid', 'meta') == Cast(OuterRef('uuid'), TextField())
).values('assignee__username')[:1]

# 关联查询并添加assignee用户名注解
applications_with_assignee = Application.objects.annotate(
    assignee_username=Subquery(assignment_subquery)
)

效果验证 #

修改后的代码会生成符合预期的SQL：

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";

此时meta ->> 'uuid'会将JSON字段中的uuid转为文本，和application.uuid的文本格式值正确匹配。

补充说明 #

• KeyTextTransform是Django对PostgreSQL JSON/JSONB字段文本提取操作的ORM封装，无需手写RawSQL即可实现需求。
• 若Application.uuid是UUIDField类型，Cast(OuterRef('uuid'), TextField())是必要的，保证两边的匹配值类型统一。

内容的提问来源于stack exchange，提问作者Andrew Zheng