How to execute an aggregate query in Django?

Question

Django

The following task has been set:

The 'Account' user collection contains documents of the form:

{
  'number': '7800000000000',
  'name': 'Пользователь №',
  'sessions': [
    {
      'created_at': ISODate('2016-01-01T00:00:00'),
      'session_id': '6QBnQhFGgDgC2FDfGwbgEaLbPMMBofPFVrVh9Pn2quooAcgxZc',
      'actions': [
        {
          'type': 'read',
          'created_at': ISODate('2016-01-01T01:20:01'),
        },
        {
          'type': 'read',
          'created_at': ISODate('2016-01-01T01:21:13'),
        },
        {
          'type': 'create',
          'created_at': ISODate('2016-01-01T01:33:59'),
        }
      ],
    }
  ]
}

It is necessary to write an aggregation query that for each user will display the last action and the total number for each of the 'actions' types. The final data should be a list of documents of the form:

{
  'number': '7800000000000',
  'actions': [
    {
      'type': 'create',
      'last': 'created_at': ISODate('2016-01-01T01:33:59'),
      'count': 12,
    },
    {
      'type': 'read',
      'last': 'created_at': ISODate('2016-01-01T01:21:13'),
      'count': 12,
    },
    {
      'type': 'update',
      'last': null,
      'count': 0,
    },
    {
      'type': 'delete',
      'last': null,
      'count': 0,
    },
  ]
}

Wrote three such models:

class Account(Model):
    number = AutoField(primary_key=True, verbose_name='Номер')
    name = CharField(max_length=20, verbose_name='Имя')

class Session(Model):
    user = ForeignKey(Account, on_delete=CASCADE, related_name='sessions', verbose_name='Пользователь')
    session_id = CharField(max_length=session_id_max_length, blank=True, editable=False, unique=True, verbose_name='ID сессии')
    created_at = DateTimeField(auto_now_add=True, verbose_name='Дата создания')

class Action(Model):

    class Type(TextChoices):
        CREATE = 'create', 'Создание'
        READ = 'read', 'Чтение'
        UPDATE = 'update', 'Изменение'
        DELETE = 'delete', 'Удаление'

    session = ForeignKey(Session, on_delete=CASCADE, related_name='actions', verbose_name='Сессия')
    type = CharField(max_length=6, choices=Type.choices, verbose_name='Тип')
    created_at = DateTimeField(auto_now_add=True, verbose_name='Дата создания')

As you can see, the tables are chained together via FK, Account <- Session <- Action, that is, you cannot get objects of the Action model directly from the Account model.
All I've been able to achieve so far is a request like

Action.objects.values('session__user__number', 'type').annotate(last=Max('created_at'), count=Count('type'))

It returns a QuerySet like

<QuerySet [
{'session__user__number': 1, 'type': 'update', 'last': datetime.datetime(2021, 6, 16, 16, 56, 55, 370580, tzinfo=<UTC>), 'count': 1}, 
{'session__user__number': 2, 'type': 'read', 'last': datetime.datetime(2021, 6, 16, 17, 23, 17, 215523, tzinfo=<UTC>), 'count': 1},
{'session__user__number': 2, 'type': 'update', 'last': datetime.datetime(2021, 6, 16, 16, 56, 11, 916884, tzinfo=<UTC>), 'count': 2}
]>

which is close enough, but no grouping by user ('session__user__number': 2 is duplicated twice).
Is there any way to get the result as close as possible to the sample without additional manipulations with the QuerySet after the query is executed?

Answer 1

There is no grouping by number, because there is a grouping by number, type.