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

Question

I'm using Django 1.9 with its built-in JSONField and Postgres 9.4. In my model's attrs json field I store objects with some values, including numbers. And I need to aggregate over them to find min/max values. Something like this:

Model.objects.aggregate(min=Min('attrs__my_key'))

Also, it would be useful to extract specific keys:

Model.objects.values_list('attrs__my_key', flat=True)

The above queries fail with

FieldError: "Cannot resolve keyword 'my_key' into field. Join on 'attrs' not permitted."

Is it possible somehow?

Notes:

1. I know how to make a plain Postgres query to do the job, but am searching specifically for an ORM solution to have the ability to filter etc.
2. I suppose this can be done with a (relatively) new query expressions/lookups API, but I haven't studied it yet.

Answer 1

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.