id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
30085	Aggregate functions crash when using Postgres JSON field KeyTransform	Daniel Zitei	nobody	"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
}}}
"	Bug	closed	contrib.postgres	2.1	Normal	duplicate	Django ORM, QUERY, Postgresql		Unreviewed	0	0	0	0	0	0