Best way of using Django queryset where JSONField != {}

45 Views Asked by At
class JobAnalysis(Base, XYZ):
    env_vars = JSONField(
        default=dict
    )
    job = models.ForeignKey(
        Job, related_name='jobanalyses'
    )
    seller = models.ForeignKey(
        ABC,
        null=True
    )

class Usage(Base):
    job = models.ForeignKey(
        Job, null=True, blank=True
    )

I want all usages where env_vars has some key pair.

    usages_qs = Usage.objects.filter(
        job__jobanalyses__seller__isnull=True
    ).exclude(
        job__jobanalyses__env_vars__exact={}
    )

I am using above queryset to fetch all usage information where seller is null and env_vars is not equals {}

usages_qs.query

SELECT "Usage"."job",
FROM "Usage"
LEFT OUTER JOIN "Job" ON ("Usage"."job_id" = "Job"."id")
LEFT OUTER JOIN "JobAnalysis" ON ("Job"."id" = "JobAnalysis"."job_id")
WHERE ("JobAnalysis"."seller_id" IS NULL
       AND NOT ("Usage"."job_id" IN
                  (SELECT U2."job_id"
                   FROM "JobAnalysis" U2
                   WHERE U2."env_vars" = '{}')
                AND "Usage"."job_id" IS NOT NULL))

But I am seeing performance issue here because .exclude(job__jobanalyses__env_vars__exact={}) create inner query and because of that this select statement is timing out.

Is there any better way of writing Django queryset for getting all usage record where seller is null and env_vars != {}?

0

There are 0 best solutions below