使用JSONField的属性排序Django查询集 [英] Ordering Django querysets using a JSONField's properties

问题描述

我有一个类似如下的模型：

I have a model that kinda looks like this:

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

data 字段具有2个属性，名称和年龄。现在，假设我想获得一个分页查询集（每个页面包含20个人），并使用一个过滤器，其中 age 大于25，并且该查询集将在以下位置进行排序降序。在通常的设置（即规范化的数据库）中，我可以这样编写该查询：

The data field has 2 properties, name, and age. Now, lets say I want to get a paginated queryset (each page containing 20 people), with a filter where age is greater than 25, and the queryset is to be ordered in descending order. In a usual setup, that is, a normalized database, I can write this query like so:

person_list_page_1 = Person.objects.filter(age > 25).order_by('-age')[:20]

现在，使用存储在JSONField中的键进行过滤和排序时，上述等效项是什么？我对此进行了研究，似乎它应该是2.1的功能，但似乎找不到任何相关的功能。

Now, what is the equivalence of the above when filtering and ordering using keys stored in the JSONField? I have researched into this, and it seems it was meant to be a feature for 2.1, but I can't seem to find anything relevant.

链接到正在实施的票证未来

我还有另一个问题。可以说我们使用JSONField进行过滤和排序。在这种情况下，ORM是否必须在发送前20个对象之前先获取所有对象，对其进行过滤和排序？也就是说，性能会合理地降低吗？

I also have another question. Lets say we filter and order using the JSONField. Will the ORM have to get all the objects, filter, and order them before sending the first 20 in such a case? That is, will performance be legitimately slower?

很显然，我知道规范化的数据库在这些方面要好得多，但我的双手却束手无策。

Obviously, I know a normalized database is far better for these things, but my hands are kinda tied.

推荐答案

您可以使用postgresql sql语法提取子字段。然后，它们可以与查询集过滤器中模型上的任何其他字段一样使用。

You can use the postgresql sql syntax to extract subfields. Then they can be used just as any other field on the model in queryset filters.

from django.db.models.expressions import RawSQL
Person.objects.annotate(
    age=RawSQL("(data->>'age')::int", [])
).filter(age__gte=25).order_by('-age')[:20]

请参阅postgresql文档，了解其他运算符和功能。
在某些情况下，您可能必须添加显式的类型转换（例如， :: int ）

See the postgresql docs for other operators and functions. In some cases, you might have to add explicit typecasts (::int, for example)

https://www.postgresql.org/docs/current/static /functions-json.html

性能会比使用适当的字段慢，但这还不错。

Performance will be slower than with a proper field, but it's not bad.

这篇关于使用JSONField的属性排序Django查询集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！