Evaluating boolean expressions and annotating the result

user_9651468 0
Viewed 80

Let's say I have a model:

class Measurement(models.Model):
    measurements = JSONField(
        null=True, blank=True, help_text="Key value pairs for measurements."
    )

    timestamp = models.DateTimeField(auto_now_add=True)

    class Meta:
        get_latest_by = "timestamp"

The values within the measurements are VARIABLE amounts of key value pairs with boolean values but the values could also be null. For example:

{
    "a": true,
    "b": false, // optional
    "c": null // optional
    //etc...can be infinite amount of values.
}

I need to be able to AND those together. For example:

result_expression = F("measurements__a") & F("measurements__b") & F("measurements__c") & F("measurement__...")

Obviously, this doesn't work.

Also, how would I be able to annotate the results of that? Measurement.objects.annotate(result=result_expression).values("result")

Where the result here should be False because of the null and False values.

How would I go about doing this? I'm aware that I can do this purely with Python. I'd rather not do that. If there's a Postgres or Django way to do it, I'd rather do that.

postgresql django python
1 Answers

You can check if the three values are True with:

Measurement.objects.annotate(
    measurements__a=True,
    measurements__b=True,
    measurements__c=True
).values('result')

or you can annotate with a Case expression [Django-doc]:

from django.db.models import BooleanField, Case, Value, When

Measurement.objects.annotate(
    result=Case(
        When(
            measurements__a=True,
            measurements__b=True,
            measurements__c=True,
            value=Value(True)
        ),
        default=Value(False),
        output_field=BooleanField()
    )
).values('result')

For a variable number of keys, we can use dictionary unpacking:

from django.db.models import BooleanField, Case, Value, When

items = ['a', 'b', 'c']

Measurement.objects.annotate(
    result=Case(
        When(
            **{f'measurements__{k}': True for k in items },
            value=Value(True)
        ),
        default=Value(False),
        output_field=BooleanField()
    )
).values('result')
user_675790
Related