json - 如何使用基于 Django 中的列的键查询 JSONField

Question

我想使用 Django ORM 编写一个查询，它需要检查 a 的键JSONField，但是，我要检查的键是基于另一个模型的字段。是否有一种方法可以使用 ORM 来表达此查询，而无需使用原始 SQL。

这是我要使用的查询的修改版本，问题是statement.properties ->> sf.name.

SELECT cr.name,
       SUM(CASE
               WHEN statement.properties ->> sf.name = ANY
                    (string_to_array(sf.compliance_values, ',')) then 1
               ELSE 0 END) AS "compliant",
       COUNT(*) as "total"

FROM "statement"
       INNER JOIN "requirementset" rs on ("statement"."requirement_set_id" = rs.id)
       INNER JOIN "statementfield" sf on rs."compliance_indicator_field_id" = sf.id

GROUP BY rs.id;

这些是我正在使用的模型的简化版本：

class StatementField(BaseModel):
    # Always a CSV
    compliance_values = TextField()

class Statement(BaseModel):
    properties = JSONField()
    requirement_set = models.ForeignKey(
        RequirementSet, 
        related_name='statements', 
        on_delete=models.CASCADE,
    )

class RequirementSet(BaseModel):
    compliance_indicator_field = models.ForeignKey(
        StatementField,
        related_name='requirement_sets',
        on_delete=models.SET_NULL,
    )

Something equivalent in Python would normally be:

(Statement
 .annotate(compliant=Sum(Case(When(statement__properties__???=..., then=1))), output=IntegerField())
 .values('requirement_set', 'compliant'))

But I can't find a way to express the JSON select expression.

Some other info: I am using PostgreSQL 9.6 and Django 3.1.4

标签： jsonpostgresqldjango-models