Opened 5 years ago

Closed 5 years ago

#30085 closed Bug (duplicate)

Aggregate functions crash when using Postgres JSON field KeyTransform

Reported by: Daniel Zitei Owned by: nobody
Component: contrib.postgres Version: 2.1
Severity: Normal Keywords: Django ORM, QUERY, Postgresql
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


I want to do this query bellow in django orm, i've tried to do with the queryset example and it doesn't work, always returns the error, i want to know if is possible to solve this.
I think the problem migth be GROUP BY, and the Django ORM doens't do the GROUP BY, but this it is just a guess.


    Traceback (most recent call last):
      File "C:\Python35\lib\site-packages\django\core\handlers\", line 34, in inner
        response = get_response(request)
      File "C:\Python35\lib\site-packages\django\core\handlers\", line 126, in _get_response
        response = self.process_exception_by_middleware(e, request)
      File "C:\Python35\lib\site-packages\django\core\handlers\", line 124, in _get_response
        response = wrapped_callback(request, *callback_args, **callback_kwargs)
      File "C:\Python35\lib\site-packages\django\views\decorators\", line 54, in wrapped_view
        return view_func(*args, **kwargs)
      File "C:\Python35\lib\site-packages\rest_framework\", line 116, in view
        return self.dispatch(request, *args, **kwargs)
      File "C:\Python35\lib\site-packages\rest_framework\", line 495, in dispatch
        response = self.handle_exception(exc)
      File "C:\Python35\lib\site-packages\rest_framework\", line 455, in handle_exception
      File "C:\Python35\lib\site-packages\rest_framework\", line 492, in dispatch
        response = handler(request, *args, **kwargs)
      File "C:\Python35\lib\site-packages\drf_multiple_model\", line 112, in list
        data = query_data['serializer_class'](queryset, many=True, context=context).data
      File "C:\Python35\lib\site-packages\rest_framework\", line 765, in data
        ret = super(ListSerializer, self).data
      File "C:\Python35\lib\site-packages\rest_framework\", line 262, in data
        self._data = self.to_representation(self.instance)
      File "C:\Python35\lib\site-packages\rest_framework\", line 683, in to_representation
        self.child.to_representation(item) for item in iterable
      File "C:\Python35\lib\site-packages\django\db\models\", line 268, in __iter__
      File "C:\Python35\lib\site-packages\django\db\models\", line 1186, in _fetch_all
        self._result_cache = list(self._iterable_class(self))
      File "C:\Python35\lib\site-packages\django\db\models\", line 106, in __iter__
        for row in compiler.results_iter(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size):
      File "C:\Python35\lib\site-packages\django\db\models\sql\", line 1017, in results_iter
        results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)
      File "C:\Python35\lib\site-packages\django\db\models\sql\", line 1052, in execute_sql
        sql, params = self.as_sql()
      File "C:\Python35\lib\site-packages\django\db\models\sql\", line 449, in as_sql
        extra_select, order_by, group_by = self.pre_sql_setup()
      File "C:\Python35\lib\site-packages\django\db\models\sql\", line 55, in pre_sql_setup
        group_by = self.get_group_by( + extra_select, order_by)
      File "C:\Python35\lib\site-packages\django\db\models\sql\", line 130, in get_group_by
        if (sql, tuple(params)) not in seen:
    TypeError: unhashable type: 'list'

Postgres query:

    SELECT (Count(jsonb(conversa->'parse_data'->'intent'->'name'))) as quantidade,
    	   jsonb(conversa->'parse_data'->'intent'->'name') as intent,
    	   to_char(data,'Mon') as mon
    FROM conversas
    where conversa->'text' is not null and conversa->'parse_data' is not null
    group by intent, mon

Django ORM:


    queryset =  Conversas.objects.extra(select={'mon':"to_char(data,'Mon')"}
                                ).values('mon', 'intent','quantidade'

Jsonb class:

class Jsonb(Func):
     function = 'jsonb'
     template = '%(function)s(%(expressions)s)'
     arity = 1

Change History (4)

comment:1 by Daniel Zitei, 5 years ago

Type: UncategorizedBug

comment:2 by Tim Graham, 5 years ago

Component: Database layer (models, ORM)contrib.postgres
Resolution: duplicate
Status: newclosed
Summary: Postgres query to Django ORMAggregate functions crash when using Postgres JSON field KeyTransform

Looks like a duplicate of #29139. The traceback is similar. Feel free to reopen if it looks like a different issue to you.

comment:3 by Daniel Zitei, 5 years ago

Resolution: duplicate
Status: closednew

But the error continues,, this link is the PR with the solution, but is not implemented in library as can you see in source code

comment:4 by Tim Graham, 5 years ago

Resolution: duplicate
Status: newclosed

#29139 is still open. We don't keep multiple tickets open for the same issue.

Note: See TracTickets for help on using tickets.
Back to Top