如何用Django ORM结合PostgreSQL将列值作为JSON字段键构建查询

嘿，我帮你把这条SQL转换成Django ORM语句，顺便拆解下逻辑，这样你也能明白背后的对应关系～

先明确模型定义 #

首先得对应你的数据库表结构，先写出Django模型（如果实际类名不一样，改一下就行）：

from django.db import models
from django.contrib.postgres.fields import JSONField

class ModelA(models.Model):
    data = JSONField()  # Django 3.1及以上版本也可以直接用models.JSONField()

    class Meta:
        db_table = "A"  # 对应数据库表名A

class ModelB(models.Model):
    id_A = models.ForeignKey(ModelA, on_delete=models.CASCADE, db_column="id_a")
    name = models.CharField(max_length=255)
    t_name = models.SlugField()

    class Meta:
        db_table = "B"  # 对应数据库表名B
        unique_together = ("id_A", "t_name")  # 实现id_A与t_name组合唯一的约束

核心ORM查询语句 #

下面就是完全匹配你那条SQL逻辑的Django ORM写法：

from django.db.models import F

# 执行查询
query_result = ModelA.objects.filter(
    # 关联ModelB，筛选name为'AA'的关联记录
    modelb__name='AA',
    # 动态引用ModelB的t_name字段，匹配data中对应键的值为'value a'
    data__contains={F('modelb__t_name'): 'value a'}
).annotate(
    # 把ModelB的name字段加入结果集，对应SQL里的bt.name
    b_name=F('modelb__name')
).values('id', 'data', 'b_name')[:50]  # 限制返回50条，对应LIMIT 50

逻辑拆解（对应你的SQL） #

• modelb__name='AA'：自动处理ModelA和ModelB的JOIN关联，同时筛选出表B中name为'AA'的记录，对应SQL里的JOIN "B" AS bt ON at.id=bt.id_a WHERE bt.name='AA'
• data__contains={F('modelb__t_name'): 'value a'}：用F()表达式动态引用关联表ModelB的t_name字段，Django会自动把这个转换成PostgreSQL的data ->> bt.t_name = 'value a'语法，实现JSON字段的键值匹配
• annotate(b_name=F('modelb__name'))：把表B的name字段添加到查询结果中，对应SQL里的SELECT ... bt.name
• values('id', 'data', 'b_name')[:50]：指定要返回的字段，并且限制最多50条结果，对应SQL的SELECT at.id, at.data, bt.name ... LIMIT 50

兼容低版本Django的写法 #

如果你的Django版本比较低（比如低于3.0），data__contains配合F()表达式可能不生效，这时候可以手动用Func构造JSON提取函数：

from django.db.models import Func, F, Value, Q

# 自定义JSON提取函数，适配PostgreSQL
class JsonExtract(Func):
    function = 'jsonb_extract_path_text'  # 如果你的data字段是json类型，改成json_extract_path_text
    template = "%(function)s(%(expressions)s)"

# 构造查询
query_result = ModelA.objects.filter(
    Q(modelb__name='AA') &
    Q(JsonExtract('data', F('modelb__t_name')) == Value('value a'))
).annotate(b_name=F('modelb__name')).values('id', 'data', 'b_name')[:50]

验证结果 #

用你提供的示例数据，这个查询会返回表A中id为1的那条记录，完全符合预期。

内容的提问来源于stack exchange，提问作者Andro Gonzalez Silvera