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

问题描述

我正在使用django 1.9及其内置的JSONField和Postgres 9.4。
在我的模型的 attrs json字段中，我用一些值存储对象，包括数字。并且我需要聚合它们以找到最小/最大值。
这样的东西：

  Model.objects.aggregate（min = Min（'attrs__my_key'））
  

此外，提取特定的键也是有用的：

  Model.objects.values_list（'attrs__my_key'，flat = True）
  

以上查询失败， FieldError ：无法将关键字'my_key'解析为字段，不允许加入'attrs'。

有可能吗？

注意：
1）我知道如何使简单的postgres查询来完成这项工作，所以我搜索专门为ORM解决方案有能力过滤等等
2）我想这可以用（相对）新的查询表达式/查找api，但我还没有研究它。

解决方案

对于有兴趣的人，我找到了解决方案（或至少解决方法）。

  from django.db.models.expressions import RawSQL 
 
 Model.objec ts.annotate（
 val = RawSQL（（（attrs->>％s）:: numeric），（json_field_key，））
）.aggregate（min = Min（'val' ）
  

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

///有点偏离///

还有一个棘手的问题与django本身无关，但需要以某种方式处理。因为 attrs 是json字段，并且对它的键和值没有限制（根据你的应用程序逻辑），可以得到一些非数字值，例如 width 键。在这种情况下，由于执行上述查询，您将从postgres获取 DataError 。 NULL值将被忽略，所以没关系。如果你可以抓住错误然后没有问题，你很幸运。在我的情况下，我需要忽略错误的值，唯一的方法是编写自定义postgres函数，这将阻止投射错误。

 创建或替换函数safe_cast_to_numeric（text）返回数字为$$ 
 begin 
 return cast（$ 1为数字）; 
 exception 
当invalid_text_representation然后
返回null; 
结束
 $$ language plpgsql immutable; 
  

然后使用它将文本转换为数字：

$ b $ （
val = RawSQL（safe_cast_to_numeric（attrs->>％s），（json_field_key，））
）.aggregate（min = Min（'val'）

因此，我们得到了相当坚实的解决方案动态的东西就像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)

The above queries fail with FieldError: "Cannot resolve keyword 'my_key' into field. Join on 'attrs' not permitted."

Is it possible somehow?

Notes: 1) I know how to make plain postgres query to do the job so I search specifically for ORM solution to have the ability to filter etc. 2) I suppose this can be done with (relatively) new query expressions/lookups api but I haven't studied it yet.

解决方案

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')

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 ///

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')

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

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