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.
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')