Django aggregation Many To Many into list of dict

Question

It's been hours since I tried to perform this operation but I couldn't figure it out.

Let's say I have a Django project with two classes like these:

from django.db import models

class Person(models.Model):
   name=models.CharField()
   address=models.ManyToManyField(to=Address)

class Address(models.Model):
   city=models.CharField()
   zip=models.IntegerField()

So it's just a simple Person having multiple addresses. Then I create some objects:

addr1=Address.objects.create(city='first', zip=12345)
addr2=Address.objects.create(city='second', zip=34555)
addr3=Address.objects.create(city='third', zip=5435)
person1=Person.objects.create(name='person_one')
person1.address.set([addr1,addr2])
person2=Person.objects.create(name='person_two')
person2.address.set([addr1,addr2,addr3])

Now it comes the hard part, I want to make a single query that will return something like that:

result = [
    {
      'name': 'person_one',
      'addresses': [
          {
            'city':'first',
            'zip': 12345
          },
          {
              'city': 'second',
              'zip': 34555
          }
      ]  
    },
    {
        'name': 'person_two',
        'addresses': [
            {
                'city':'first',
                'zip': 12345
            },
            {
                'city': 'second',
                'zip': 34555
            },
            {
                'city': 'third',
                'zip': 5435
            }
        ]
    }
]

The best i could get was using ArrayAgg and JSONBAgg operators for Django (I'm on POSTGRESQL BY THE WAY):

from django.contrib.postgres.aggregates import JSONBAgg, ArrayAgg
result = Person.objects.values(
   'name',
   addresses=JSONBAgg('city')
)

But that's not enough, I can't pull a lit of dictionaries out of the query directly as I would like to do, just a list of values or something useless using:

addresses=JSONBAgg(('city','zip'))

which returns a dictionari with random keys and the strings I passed as input as values.

Can someone help me out?

Thanks

Answer 1

person.address already holds a queryset of addresses. From there you can use list-comprehension / model_from_dict to get the values you want.

Answer 2

Your requirement: To make an aggregation of customized JSON objects after group_by (values) in Django.

Currently, to my knowledge, Django is not providing any function to aggregate manually created JSON objects. There are a couple of ways to solve this. Firstly, make a customized function which is quite laborious. However, there is another approach that is pretty much easy, using both aggregate functions (ArrayAgg or JSONBAgg) and RawSQL together.

from django.contrib.postgres.aggregates import JSONBAgg, ArrayAgg
result = Person.objects.values('name').annotate(addresses=JSONBAgg(RawSQL("json_build_object('city', city, 'zip', zip)", ())))

I hope it would help you.