How do I make a list of field values on annotated Django queryset results?

Question

This may be out there somewhere, but not quite sure how to ask it. Hopefully it will be a quick one. Say I have a table like the following:

name         count   site
Guy Man      2       ABC
Guy Man      3       BCD
Guy Man      4       CDE
Girl Woman   2       ABC
Girl Woman   2       BCD
Girl Woman   3       CDE

I would like to annotate these, so that I get a name, total count, and list of sites. So given the data above, I'd get the following.

[
  {
    "name": "Guy Man",
    "count_total": 9,
    "site_list": "ABC, BCD, CDE"
  },
  {
    "name": "Girl Woman",
    "count_total": 7,
    "site_list": "ABC, BCD, CDE"
  }
]

I understand how to get count_total, but I'm not sure how to get site_list. Any ideas?

score 5 · Answer 1 · answered Aug 12 '19 at 14:56

Solution for PostgreSQL grouping by name field in combination with ArrayAgg and the distinct=True flag:

from django.contrib.postgres.aggregates.general import ArrayAgg
from django.db.models import Count

Model.objects.values('name').annotate(
    count_total=Count('count'),
    site_list=ArrayAgg('site', distinct=True),
)

score 1 · Answer 2 · answered Aug 12 '19 at 14:54

If you are using postgres you can use ArrayAgg

docs - https://docs.djangoproject.com/en/2.2/ref/contrib/postgres/aggregates/

You can use values to group by and something like the following should do the trick. Model.objects.values('name').distinct().annotate(site_list=ArrayAgg('site'), count_total=Count('count'))

How do I make a list of field values on annotated Django queryset results?

2 Answers2