I'm trying to aggregate Sum() to a queryset, the fields are simple IntegerField() but this error appears.

Error:

The above exception was the direct cause of the following exception:
 
 Traceback (most recent call last):
   File "/usr/local/lib/python3.6/site-packages/django/core/handlers/exception.py", line 34, in inner
     response = get_response(request)
   File "/usr/local/lib/python3.6/site-packages/django/core/handlers/base.py", line 115, in _get_response
     response = self.process_exception_by_middleware(e, request)
   File "/usr/local/lib/python3.6/site-packages/django/core/handlers/base.py", line 113, in _get_response
     response = wrapped_callback(request, *callback_args, **callback_kwargs)
   File "/usr/local/lib/python3.6/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
     return view_func(*args, **kwargs)
   File "/usr/local/lib/python3.6/site-packages/django/views/generic/base.py", line 71, in view
     return self.dispatch(request, *args, **kwargs)
   File "/usr/local/lib/python3.6/site-packages/rest_framework/views.py", line 495, in dispatch
     response = self.handle_exception(exc)
   File "/usr/local/lib/python3.6/site-packages/rest_framework/views.py", line 455, in handle_exception
     self.raise_uncaught_exception(exc)
   File "/usr/local/lib/python3.6/site-packages/rest_framework/views.py", line 492, in dispatch
     response = handler(request, *args, **kwargs)
   File "/opt/companies/api/v2/views.py", line 86, in get
     ticket_closed_time=Avg(F("ticket_closed_time")),
   File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 379, in aggregate
     return query.get_aggregation(self.db, kwargs)
   File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/query.py", line 489, in get_aggregation
     result = compiler.execute_sql(SINGLE)
   File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1100, in execute_sql
     cursor.execute(sql, params)
   File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 99, in execute
     return super().execute(sql, params)
   File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 67, in execute
     return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
   File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
     return self.cursor.execute(sql, params)
   File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
     raise dj_exc_value.with_traceback(traceback) from exc_value
   File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
     return self.cursor.execute(sql, params)
 django.db.utils.ProgrammingError: function sum(jsonb) does not exist
 LINE 1: ...aggregation"."ticket_closed") AS "ticket_closed", SUM("ticke...
                                                              ^
 HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Code:

 ticket_history_aggregation = TicketHistoryAggregation.objects.filter(
            aggregation_date__date__gte=date_from,
            aggregation_date__date__lte=date_to,
            category=categories,
            # stores=stores,
            team=teams,
        )

if ticket_history_aggregation.count() > 1:

            ticket_history_aggregation = ticket_history_aggregation.aggregate(
                ticket_total=Sum("ticket_total"),
                ticket_queue=Sum("ticket_queue"),
                ticket_queue_expiring=Sum("ticket_queue_expiring"),
                ticket_processing_expiring=Sum("ticket_processing_expiring"),
                ticket_pending=Sum("ticket_pending"),
                ticket_solved=Sum("ticket_solved"),
                ticket_unapplicable=Sum("ticket_unapplicable"),
                ticket_processing=Sum("ticket_processing"),
                ticket_closed=Sum("ticket_closed"),
                ticket_store=Sum("ticket_store"),
                ticket_store_managed=Sum("ticket_store_managed"),
                ticket_store_total=Sum("ticket_store_total"),
                ticket_queue_w_time=Avg("ticket_queue_w_time"),
                ticket_pending_w_time=Avg("ticket_pending_w_time"),
                ticket_processing_w_time=Avg("ticket_processing_w_time"),
                ticket_working_w_time=Avg("ticket_working_w_time"),
                ticket_closed_w_time=Avg("ticket_closed_w_time"),
               # ticket_public_post_processed_w_time=Sum(
               #     F("ticket_public_post_processed_w_time")
               # ),
                ticket_queue_time=Avg("ticket_queue_time"),
                ticket_pending_time=Avg("ticket_pending_time"),
                ticket_processing_time=Avg("ticket_processing_time"),
                ticket_working_time=Avg("ticket_working_time"),
                ticket_closed_time=Avg("ticket_closed_time"),
            )

Model:

class TicketHistoryAggregation(models.Model):

    ticket_total = models.IntegerField(_("Ticket Totali"),default=0)
    ticket_queue = models.IntegerField(_("Totale Ticket Queue"), default=0)
    ticket_queue_expiring = models.IntegerField(_("Totale Ticket Queue Expiring"), default=0)
    ticket_processing_expiring = models.IntegerField(_("Totale Ticket Procesing Expiring"), default=0)
    ticket_pending = models.IntegerField(_("Totale Ticket Pending"), default=0)
    ticket_solved = models.IntegerField(_("Totale Ticket Solved"), default=0)
    ticket_unapplicable = models.IntegerField(_("Totale Ticket Unapplicable"), default=0)
    ticket_processing = models.IntegerField(_("Totale Ticket Processing"), default=0)
    ticket_closed = models.IntegerField(_("Totale Ticket Closed"), default=0)
    ticket_public_post_processed = models.IntegerField(_("Totale Ticket Public Post Processed"), default=0)
    ticket_store = JSONField(_("Totale Ticket Stored"), blank=True, default=dict)
    ticket_store_managed = JSONField(_("Totale Ticket Managed Stored"), blank=True, default=dict)
    ticket_store_total = JSONField(_("Totale Ticket Total Stored"), blank=True, default=dict)


    ticket_queue_w_time = models.IntegerField(_("Totale Tempo Queue"), default=0)
    ticket_pending_w_time = models.IntegerField(_("Totale Tempo Pending"), default=0)
    ticket_processing_w_time = models.IntegerField(_("Totale Tempo Processing"), default=0)
    ticket_working_w_time = models.IntegerField(_("Totale Tempo Lavorazione"), default=0)
    ticket_closed_w_time = models.IntegerField(_("Totale Tempo Closed"), default=0)
    ticket_public_post_processed_w_time = models.IntegerField(_("Totale Tempo Public Post Processed"), default=0)


    ticket_queue_time = models.IntegerField(_("Totale Tempo Queue"), default=0)
    ticket_pending_time = models.IntegerField(_("Totale Tempo Pending"), default=0)
    ticket_processing_time = models.IntegerField(_("Totale Tempo Processing"), default=0)
    ticket_working_time = models.IntegerField(_("Totale Tempo Lavorazione"), default=0)
    ticket_closed_time = models.IntegerField(_("Totale Tempo Closed"), default=0)

    ticket_public_post_processed_time = models.IntegerField(_("Totale Tempo Public Post Processed"), default=0)

Django Ver: 2.2.3

Postgres: 12

I tried with: ticket_queue=Sum(F("ticket_closed")), and ticket_queue=Sum(Cast(F("ticket_closed"), output_field=IntegerField())

But the error is still there, it's the first time it happens to me, by the way they are simple integer fields. Do you know what I can feel or where I'm wrong? Thanks for the help

CodePudding user response：

I think there lies the issue: you are trying to Sum several fields that are declared as JSONField on your model:

ticket_store=Sum("ticket_store"),
ticket_store_managed=Sum("ticket_store_managed"),
ticket_store_total=Sum("ticket_store_total"),

You either have an issue in your model definition or in the aggregate clause