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
For PostgreSQL, Django has an
ArrayAgg
function [Django-doc]:You can use
groupby
fromitertools
, then create the aggregate for any databases:First create your own aggregate like this
After creating this use below query
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’sJSONField
:(Don’t really need the overridden
__init__()
, unless you want theDISTINCT
functionality, in which case can also just useallow_distinct=True
)And then as @deepak-tripathi says too, to use it:
(the
isnull
filter is to avoid[None]
in aggregated results)