Aggregate functions crash when using Postgres JSON field KeyTransform

[Daniel Zitei]

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.

error:

    Traceback (most recent call last):
      File "C:\Python35\lib\site-packages\django\core\handlers\exception.py", line 34, in inner
        response = get_response(request)
      File "C:\Python35\lib\site-packages\django\core\handlers\base.py", line 126, in _get_response
        response = self.process_exception_by_middleware(e, request)
      File "C:\Python35\lib\site-packages\django\core\handlers\base.py", line 124, in _get_response
        response = wrapped_callback(request, *callback_args, **callback_kwargs)
      File "C:\Python35\lib\site-packages\django\views\decorators\csrf.py", line 54, in wrapped_view
        return view_func(*args, **kwargs)
      File "C:\Python35\lib\site-packages\rest_framework\viewsets.py", line 116, in view
        return self.dispatch(request, *args, **kwargs)
      File "C:\Python35\lib\site-packages\rest_framework\views.py", line 495, in dispatch
        response = self.handle_exception(exc)
      File "C:\Python35\lib\site-packages\rest_framework\views.py", line 455, in handle_exception
        self.raise_uncaught_exception(exc)
      File "C:\Python35\lib\site-packages\rest_framework\views.py", line 492, in dispatch
        response = handler(request, *args, **kwargs)
      File "C:\Python35\lib\site-packages\drf_multiple_model\mixins.py", line 112, in list
        data = query_data['serializer_class'](queryset, many=True, context=context).data
      File "C:\Python35\lib\site-packages\rest_framework\serializers.py", line 765, in data
        ret = super(ListSerializer, self).data
      File "C:\Python35\lib\site-packages\rest_framework\serializers.py", line 262, in data
        self._data = self.to_representation(self.instance)
      File "C:\Python35\lib\site-packages\rest_framework\serializers.py", line 683, in to_representation
        self.child.to_representation(item) for item in iterable
      File "C:\Python35\lib\site-packages\django\db\models\query.py", line 268, in __iter__
        self._fetch_all()
      File "C:\Python35\lib\site-packages\django\db\models\query.py", line 1186, in _fetch_all
        self._result_cache = list(self._iterable_class(self))
      File "C:\Python35\lib\site-packages\django\db\models\query.py", 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\compiler.py", 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\compiler.py", line 1052, in execute_sql
        sql, params = self.as_sql()
      File "C:\Python35\lib\site-packages\django\db\models\sql\compiler.py", 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\compiler.py", line 55, in pre_sql_setup
        group_by = self.get_group_by(self.select + extra_select, order_by)
      File "C:\Python35\lib\site-packages\django\db\models\sql\compiler.py", 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:

Repository:

    queryset =  Conversas.objects.extra(select={'mon':"to_char(data,'Mon')"}
                                ).filter(conversa__text__isnull=False,
                                         conversa__parse_data__isnull=False,
                                         data__gte=last_day,
                                         data__lte=today
                                ).annotate(intent=KeyTransform('name',
                                                        KeyTransform('intent',
                                                        KeyTransform('parse_data','conversa'))),
                                            quantidade=Count(Jsonb(KeyTransform('name',
                                                                   KeyTransform('intent',
                                                                   KeyTransform('parse_data','conversa')))))
                                ).values('mon', 'intent','quantidade'
                                ).order_by('mon','intent')

Jsonb class:

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

[Tim Graham]

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

[Daniel Zitei]

But the error continues,​https://github.com/django/django/pull/10240/files, this link is the PR with the solution, but is not implemented in library as can you see in source code ​https://github.com/django/django/blob/master/django/contrib/postgres/fields/jsonb.py

[Tim Graham]

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