如何在Django模型的WHERE条件中使用jsonb_array_elements

方法1：纯Django ORM实现（推荐） #

利用Django的Exists、Subquery和PostgreSQL JSONB函数，完全通过ORM构建查询，既避免原生SQL的注入风险，又保留ORM的灵活性。

先导入依赖模块：

from django.db.models import Exists, Subquery, OuterRef, Func
from django.db.models.functions import KeyTextTransform

然后构建查询逻辑：

# 替换为你的目标UUID列表
target_uuids = ["uuid1", "uuid2", ...]

# 1. 定义子查询：展开当前Profile的product_uuids并筛选不在目标列表中的项
expanded_product_uuids = Profile.objects.filter(
    pk=OuterRef('pk')  # 关联外层Profile主键，确保子查询仅针对当前记录
).annotate(
    # 将criteria中的product_uuids数组展开为文本格式的UUID
    product_uuid=Func(
        KeyTextTransform('product_uuids', 'criteria'),
        function='jsonb_array_elements_text'
    )
).values('product_uuid')

# 2. 筛选出包含无效UUID的子查询结果
invalid_products_subquery = expanded_product_uuids.exclude(
    product_uuid__in=target_uuids
)

# 3. 最终查询：同时满足三个筛选条件
valid_profiles = Profile.objects.filter(
    deleted_at__isnull=True,
    criteria__product_uuids__isnull=False,  # 确保product_uuids字段不为null
    ~Exists(invalid_products_subquery)  # 不存在不在目标列表中的UUID
)

方法2：原生SQL快速实现 #

如果ORM写法过于繁琐，可以直接复用你原本的SQL逻辑，通过RawSQL安全传递参数：

from django.db.models import RawSQL

target_uuids = ["uuid1", "uuid2", ...]
# 转换为PostgreSQL可识别的JSON格式字符串
target_json_str = str(target_uuids).replace("'", '"')

valid_profiles = Profile.objects.filter(
    deleted_at__isnull=True,
    criteria__product_uuids__isnull=False,
    # 用~取反实现NOT EXISTS逻辑
    ~RawSQL(
        """
        EXISTS(
            SELECT product_uuid::varchar
            FROM jsonb_array_elements(criteria -> 'product_uuids') as product_uuid
            WHERE product_uuid::varchar NOT IN (
                SELECT my_product::varchar 
                FROM jsonb_array_elements(%s::jsonb) as my_product
            )
        )
        """,
        [target_json_str]  # 安全传递参数，避免SQL注入
    )
)

问题原因说明 #

你之前尝试直接使用jsonb_array_elements时，没有通过OuterRef关联外层Profile的主键，导致子查询无法和外层记录绑定，生成的SQL自然没有正确关联。方法1中的pk=OuterRef('pk')是关键，它让子查询仅针对当前外层的Profile记录展开其product_uuids数组。

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