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

Question

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.

Leave a Comment Cancel reply