Django使用Postgres在JSONField中注释计数 [英] Django annotate count in JSONField with Postgres

问题描述

使用Django，我有一个JSONField类型的字段。我想对json中的嵌套键/值进行不同的计数。使用普通字段，您可以像下面这样

Using Django I have a field which is of type JSONField. I am wanting to get a distinct count on a nested key/value in the json. With a normal field you can just do soemthing like the following

model.objects.values('field_name')\
.annotate(total=Count('field_name')).order_by('-total')

这不适用于JSONField。

This does not work for a JSONField.

示例模型

class Pet(models.Model):
    data = JSONField()

数据示例

  {
    'name':'sparky',
    'animal':'dog',
    'diet':{
        'breakfast':'biscuits',
        'dinner':'meat',
    }
}

尝试

Pet.objects.values('data__diet__dinner')\
.annotate(total=Count('data__diet__dinner')).order_by('-total')

TypeError: unhashable type: 'list'

执行此操作的正确方法是什么？

What is the correct way to execute this?

解决方案

您可以使用 jsonb_extract_path_text 通过 Func 对象作为字段变换的替代方法：

You can use jsonb_extract_path_text via a Func object as an alternative to the field transform:

Pet.annotate(dinner=Func(
    F('data'), Value('diet'), Value('dinner'),
    function='jsonb_extract_path_text'))  \
.values('dinner')  \
.annotate(total=Count('dinner'))

字段转换为<$ c的原因$ c> data__diet__dinner 失败是Django中的一个错误，当您深入到json结构中不止一层并且使用 GROUP BY 在SQL中。第一层（名称，动物，饮食）

The reason why the field transform data__diet__dinner fails is an error within Django when you go deeper than just one level into the json structure and use GROUP BY in the SQL. The first level (name, animal, diet) should work fine.

原因似乎是，对于嵌套转换，Django更改了所使用的SQL语法，从单个值切换为列表以指定指向json的路径结构体。

The reason seems to be that for nested transforms, Django changes the SQL syntax used, switching from a single value to a list to specify the path into the json structure.

这是用于非嵌套json转换（=第一级）的语法：

This is the syntax used for non-nested json transforms (= first level):

"appname_pet"."data" -> 'diet'

这是嵌套转换的语法（比第一层还深）：

And this is the syntax used for nested transforms (deeper than first level):

"appname_pet"."data" #> ARRAY['diet', 'dinner']

在构造查询时，该列表上的Django阻塞了计算出必需的 GROUP BY 子句。这似乎不是必然的限制；对转换的支持是非常新的，这可能是尚未解决的问题之一。因此，如果您打开 Django票证，可能只适用于某些版本。

While constructing the query, Django chokes on that list while working out the required GROUP BY clauses. This does not seem to be an inevitable restriction; the support for transforms is quite new, and this is possibly one of the kinks that haven't been worked out yet. So if you open a Django ticket, this might just work a few versions down the line.

这篇关于Django使用Postgres在JSONField中注释计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！