[Django]-How to aggregate (min/max etc.) over Django JSONField data?

From django 4.2 There is the new KT() expression that makes all of this a bit clearer. It’s also secretly just KeyTextTransform under the hood

Model.objects.annotate(
    val=KT('json_field__key')
).aggregate(min=Min('val'))

From django 1.11 (which isn’t out yet, so this might change) you can use django.contrib.postgres.fields.jsonb.KeyTextTransform instead of RawSQL .

In django 1.10 you have to copy/paste KeyTransform to you own KeyTextTransform and replace the -> operator with ->> and #> with #>> so it returns text instead of json objects.

Model.objects.annotate(
    val=KeyTextTransform('json_field_key', 'blah__json_field'))
).aggregate(min=Min('val')

You can even include KeyTextTransforms in SearchVectors for full text search

Model.objects.annotate(
    search=SearchVector(
        KeyTextTransform('jsonb_text_field_key', 'json_field'))
    )
).filter(search='stuff I am searching for')

Remember you can also index in jsonb fields, so you should consider that based upon your specific workload.

I know this is a bit late (several months) but I came across the post while trying to do this. Managed to do it by:

1) using KeyTextTransform to convert the jsonb value to text

2) using Cast to convert it to integer, so that the SUM works:

q = myModel.objects.filter(type=9) \
.annotate(numeric_val=Cast(KeyTextTransform(sum_field, 'data'), IntegerField()))  \
.aggregate(Sum('numeric_val'))

print(q)

where ‘data’ is the jsonb property, and ‘numeric_val’ is the name of the variable I create by annotating.

Hope this helps somebody!

It is possible to do this using a Postgres function

https://www.postgresql.org/docs/9.5/functions-json.html

from django.db.models import Func, F, FloatField
from django.db.models.expressions import Value
from django.db.models.functions import Cast

text = Func(F(json_field), Value(json_key), function='jsonb_extract_path_text')
floatfield = Cast(text, FloatField())

Model.objects.aggregate(min=Min(floatfield))

This is much better than using the RawQuery because it doesn’t break if you do a more complex query, where Django uses aliases and where there are field name collisions. There is so much going on with the ORM that can bite you with hand written implementations.

Since Django 3.1 the KeyTextTransform function on a JSON field works for all database backends. It maps to the ->> operator in Postgres.

It can be used to annotate a specific JSON value inside a JSONField on the queryset results before you aggregate it. A more clear example how to utilize this:

First we need to annotate the key you want to aggregate. So if you have a Django model with a JSONField named data and the JSON containing looks like this:

{
    "age": 43,
    "name" "John"
}

You would annotate the queryset as following:

from django.db.models import IntegerField
from django.db.models.fields.json import KeyTextTransform

qs = Model.objects.annotate(
    age=Cast(
        KeyTextTransform("age", "data"), models.IntegerField()
    )

The Cast is needed to stay compatible with all database backend.

Now you can aggregate to your liking:

from django.db.models import Min, Max, Avg, IntegerField
from django.db.models.functions import Cast, Round

qs.aggregate(
    min_age=Round(Min("age")),
    max_age=Round(Max("age")),
    avg_age=Cast(Round(Avg("age")), IntegerField()),
)

>>> {'min_age': 25, 'max_age' 82:, 'avg_age': 33}

Seems there is no native way to do it.

I worked around like this:

my_queryset = Product.objects.all() # Or .filter()...
max_val = max(o.my_json_field.get(my_attrib, '') for o in my_queryset)

This is far from being marvelous, since it is done at the Python Level (and not at the SQL level).

from django.db.models.functions import Cast
from django.db.models import Max, Min

qs = Model.objects.annotate(
    val=Cast('attrs__key', FloatField())
).aggregate(
    min=Min("val"),
    max=Max("val")
)