skip to Main Content

I use SQLite and have a django model with the below data:

Name Value
A 1
B 2
B 4
C 7
C 5

I would like to use an aggregate my table so that I can get my data in the below format:

Name Value
A 1
B [2,4]
C [7,5]

How do I do this with Django.

I’m thinking this may be the answer but Django doesn’t seem to have ArrayAgg. This looks to be a PostgreSQL function

Test_Model.objects.annotate(dname=ArrayAgg('name')).values()

Do you know how I can achieve this without using PostgreSQL?
Thank you!

4

Answers


  1. For PostgreSQL, Django has an ArrayAgg function [Django-doc]:

    from django.contrib.postgres.aggregates import ArrayAgg
    
    Test_Model.objects.values('name').annotate(dname=ArrayAgg('value')).order_by('name')
    Login or Signup to reply.
  2. You can use groupby from itertools , then create the aggregate for any databases:

    >>> from itertools import groupby
    >>> [{'Name': key, 'Value': list(item.Value for item in grp)} for key, grp in
        groupby(Test_Model.objects.order_by('Name'), key=lambda x: x.Name)] 
    
    [{'Name': 'A', 'Value': [1]},
     {'Name': 'B', 'Value': [2, 4]},
     {'Name': 'C', 'Value': [7, 5]}]
    
    Login or Signup to reply.
  3. First create your own aggregate like this

    from django.db.models import Aggregate
    
    class GroupConcat(Aggregate):
        function = 'GROUP_CONCAT'
        template = '%(function)s(%(distinct)s%(expressions)s)'
    
        def __init__(self, expression, distinct=False, **extra):
            super(GroupConcat, self).__init__(
                expression,
                distinct='DISTINCT ' if distinct else '',
                output_field=CharField(),
                **extra)
    

    After creating this use below query

    Test_Model.objects.values('name').annotate(dname=GroupConcat('value')).order_by('name')
    
    Login or Signup to reply.
  4. Building on @deepak-tripathi ‘s answer, their solution returns a string. To return a list use SQLite’s JSON_GROUP_ARRAY aggregation function and Django’s JSONField:

    from django.db.models import JSONField
    from django.db.models.aggregates import Aggregate
    
    class JsonGroupArray(Aggregate):
        function = 'JSON_GROUP_ARRAY'
        output_field = JSONField()
        template = '%(function)s(%(distinct)s%(expressions)s)'
    

    (Don’t really need the overridden __init__(), unless you want the DISTINCT functionality, in which case can also just use allow_distinct=True)

    And then as @deepak-tripathi says too, to use it:

    Test_Model.objects.values('name').annotate(
        dname=JsonGroupArray('value', filter(value__isnull=False)),
    ).order_by('name')
    

    (the isnull filter is to avoid [None] in aggregated results)

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search