如何在 Django JSONField 数据上聚合(最小/最大等)? [英] How to aggregate (min/max etc.) over Django JSONField data?

问题描述

我使用的是带有内置 JSONField 和 Postgres 9.4 的 Django 1.9.在我模型的 attrs json 字段中，我存储了具有一些值(包括数字)的对象.我需要对它们进行聚合以找到最小值/最大值.像这样:

I'm using Django 1.9 with its built-in JSONField and Postgres 9.4. In my model's attrs json field I store objects with some values, including numbers. And I need to aggregate over them to find min/max values. Something like this:

Model.objects.aggregate(min=Min('attrs__my_key'))

此外，提取特定键也很有用:

Also, it would be useful to extract specific keys:

Model.objects.values_list('attrs__my_key', flat=True)

以上查询失败

FieldError:无法将关键字‘my_key’解析为字段.不允许加入‘attrs’."

FieldError: "Cannot resolve keyword 'my_key' into field. Join on 'attrs' not permitted."

有没有可能?

注意事项:

1. 我知道如何进行简单的 Postgres 查询来完成这项工作，但我正在专门寻找一个 ORM 解决方案，以便能够进行过滤等.
2. 我想这可以通过(相对)新的查询表达式/查找 API 来完成，但我还没有研究过.

推荐答案

对于那些感兴趣的人，我已经找到了解决方案(或者至少是解决方法).

For those who interested, I've found the solution (or workaround at least).

from django.db.models.expressions import RawSQL

Model.objects.annotate(
    val=RawSQL("((attrs->>%s)::numeric)", (json_field_key,))
).aggregate(min=Min('val')

请注意，attrs->>%s 表达式在处理后会变得像 attrs->>'width' 一样(我的意思是单引号).所以如果你硬编码这个名字，你应该记住插入它们，否则你会得到错误.

Note that attrs->>%s expression will become smth like attrs->>'width' after processing (I mean single quotes). So if you hardcode this name you should remember to insert them or you will get error.

///有点题外话///

/// A little bit offtopic ///

还有一个棘手的问题，与 django 本身无关，但需要以某种方式处理.由于 attrs 是 json 字段，并且对其键和值没有限制，您可以(取决于您的应用程序逻辑)获取一些非数字值，例如 width 键.在这种情况下，作为执行上述查询的结果，您将从 postgres 获得 DataError.同时 NULL 值将被忽略，所以没关系.如果你能抓住错误，那就没问题了，你很幸运.就我而言，我需要忽略错误的值，这里唯一的方法是编写自定义 postgres 函数来抑制转换错误.

And one more tricky issue not related to django itself but that is needed to be handled somehow. As attrs is json field and there're no restrictions on its keys and values you can (depending on you application logic) get some non-numeric values in, for example, width key. In this case you will get DataError from postgres as a result of executing the above query. NULL values will be ignored meanwhile so it's ok. If you can just catch the error then no problem, you're lucky. In my case I needed to ignore wrong values and the only way here is to write custom postgres function that will supress casting errors.

create or replace function safe_cast_to_numeric(text) returns numeric as $$
begin
    return cast($1 as numeric);
exception
    when invalid_text_representation then
        return null;
end;
$$ language plpgsql immutable;

然后用它来将文本转换为数字:

And then use it to cast text to numbers:

Model.objects.annotate(
    val=RawSQL("safe_cast_to_numeric(attrs->>%s)", (json_field_key,))
).aggregate(min=Min('val')

因此，对于像 json 这样的动态事物，我们得到了非常可靠的解决方案.

Thus we get quite solid solution for such a dynamic thing as json.

这篇关于如何在 Django JSONField 数据上聚合(最小/最大等)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！