Skip to content

Instantly share code, notes, and snippets.

@alej0varas

alej0varas/django-jsonfield-length-sum-average.py

Created September 2, 2016 14:37
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save alej0varas/4d66cfd46e96d8c6a0bb5513afa3664c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save alej0varas/4d66cfd46e96d8c6a0bb5513afa3664c to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. alej0varas created this gist Sep 2, 2016.
    25 changes: 25 additions & 0 deletions django-jsonfield-length-sum-average.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,25 @@
    # 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')}