Skip to content

Instantly share code, notes, and snippets.

@alej0varas
Created September 2, 2016 14:37
Show Gist options
  • Save alej0varas/4d66cfd46e96d8c6a0bb5513afa3664c to your computer and use it in GitHub Desktop.
Save alej0varas/4d66cfd46e96d8c6a0bb5513afa3664c to your computer and use it in GitHub Desktop.
Example usage of database funtions(`Sum` and `Avg`) and Django `JSONField`'s `jsonb_array_length`
# Example usage of database funtions(`Sum` and `Avg`) and Django `JSONField`'s `jsonb_array_length`
# PostgreSQL json field functions: https://www.postgresql.org/docs/9.5/static/functions-json.html
# Django `F` and `Func`: https://docs.djangoproject.com/en/1.10/ref/models/expressions/#f-expressions
# Django aggregation: https://docs.djangoproject.com/en/1.10/topics/db/aggregation/
# Django `JSONField`: https://docs.djangoproject.com/en/1.9/ref/contrib/postgres/fields/#jsonfield
# Given a model
class Contact(models.Model):
numbers = JSONField(help_text="An array of numbers")
from django.db.models import Sum, Avg, F, Func
# Get the length of `numbers` field for one record
contact = Contact.objects.annotate(numbers_len=Func(F('numbers'), function='jsonb_array_length')).latest('pk')
>>> contact.numbers_len
3
# Get the sum of the length of all records
>>> contact = Contact.objects.annotate(numbers_len=Func(F('numbers'), function='jsonb_array_length')).aggregate(Sum('numbers_len'))
>>> contact
{'numbers_len__sum': 247485107} # 247MM!
# Get the average length of all `numbers`
>>> contact = Contact.objects.annotate(numbers_len=Func(F('numbers'), function='jsonb_array_length')).aggregate(Avg('numbers_len'))
>>> contact
{'numbers_len__avg': Decimal('1.9999268527384372')}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment