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:
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 KeyTextTransform
s in SearchVector
s 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With