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!
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]}]
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')
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')
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)
