These functions are available from the django.contrib.postgres.aggregates
module. They are described in more detail in the PostgreSQL docs.
Note
All functions come without default aliases, so you must explicitly provide one. For example:
>>> SomeModel.objects.aggregate(arr=ArrayAgg("somefield"))
{'arr': [0, 1, 2]}
ArrayAgg
¶Returns a list of values, including nulls, concatenated into an array, or
default
if there are no values.
An optional boolean argument that determines if array values
will be distinct. Defaults to False
.
An optional string of a field name (with an optional "-"
prefix
which indicates descending order) or an expression (or a tuple or list
of strings and/or expressions) that specifies the ordering of the
elements in the result list.
Examples:
"some_field"
"-some_field"
from django.db.models import F
F("some_field").desc()
Deprecated since version 4.0: If there are no rows and default
is not provided, ArrayAgg
returns an empty list instead of None
. This behavior is deprecated
and will be removed in Django 5.0. If you need it, explicitly set
default
to Value([])
.
BitAnd
¶BitOr
¶BitXor
¶BoolAnd
¶Returns True
, if all input values are true, default
if all values
are null or if there are no values, otherwise False
.
Usage example:
class Comment(models.Model):
body = models.TextField()
published = models.BooleanField()
rank = models.IntegerField()
>>> from django.db.models import Q
>>> from django.contrib.postgres.aggregates import BoolAnd
>>> Comment.objects.aggregate(booland=BoolAnd("published"))
{'booland': False}
>>> Comment.objects.aggregate(booland=BoolAnd(Q(rank__lt=100)))
{'booland': True}
BoolOr
¶Returns True
if at least one input value is true, default
if all
values are null or if there are no values, otherwise False
.
Usage example:
class Comment(models.Model):
body = models.TextField()
published = models.BooleanField()
rank = models.IntegerField()
>>> from django.db.models import Q
>>> from django.contrib.postgres.aggregates import BoolOr
>>> Comment.objects.aggregate(boolor=BoolOr("published"))
{'boolor': True}
>>> Comment.objects.aggregate(boolor=BoolOr(Q(rank__gt=2)))
{'boolor': False}
JSONBAgg
¶Returns the input values as a JSON
array, or default
if there are
no values. You can query the result using key and index lookups
.
An optional boolean argument that determines if array values will be
distinct. Defaults to False
.
An optional string of a field name (with an optional "-"
prefix
which indicates descending order) or an expression (or a tuple or list
of strings and/or expressions) that specifies the ordering of the
elements in the result list.
Examples are the same as for ArrayAgg.ordering
.
Usage example:
class Room(models.Model):
number = models.IntegerField(unique=True)
class HotelReservation(models.Model):
room = models.ForeignKey("Room", on_delete=models.CASCADE)
start = models.DateTimeField()
end = models.DateTimeField()
requirements = models.JSONField(blank=True, null=True)
>>> from django.contrib.postgres.aggregates import JSONBAgg
>>> Room.objects.annotate(
... requirements=JSONBAgg(
... "hotelreservation__requirements",
... ordering="-hotelreservation__start",
... )
... ).filter(requirements__0__sea_view=True).values("number", "requirements")
<QuerySet [{'number': 102, 'requirements': [
{'parking': False, 'sea_view': True, 'double_bed': False},
{'parking': True, 'double_bed': True}
]}]>
Deprecated since version 4.0: If there are no rows and default
is not provided, JSONBAgg
returns an empty list instead of None
. This behavior is deprecated
and will be removed in Django 5.0. If you need it, explicitly set
default
to Value('[]')
.
StringAgg
¶Returns the input values concatenated into a string, separated by
the delimiter
string, or default
if there are no values.
Required argument. Needs to be a string.
An optional boolean argument that determines if concatenated values
will be distinct. Defaults to False
.
An optional string of a field name (with an optional "-"
prefix
which indicates descending order) or an expression (or a tuple or list
of strings and/or expressions) that specifies the ordering of the
elements in the result string.
Examples are the same as for ArrayAgg.ordering
.
Usage example:
class Publication(models.Model):
title = models.CharField(max_length=30)
class Article(models.Model):
headline = models.CharField(max_length=100)
publications = models.ManyToManyField(Publication)
>>> article = Article.objects.create(headline="NASA uses Python")
>>> article.publications.create(title="The Python Journal")
<Publication: Publication object (1)>
>>> article.publications.create(title="Science News")
<Publication: Publication object (2)>
>>> from django.contrib.postgres.aggregates import StringAgg
>>> Article.objects.annotate(
... publication_names=StringAgg(
... "publications__title",
... delimiter=", ",
... ordering="publications__title",
... )
... ).values("headline", "publication_names")
<QuerySet [{
'headline': 'NASA uses Python', 'publication_names': 'Science News, The Python Journal'
}]>
Deprecated since version 4.0: If there are no rows and default
is not provided, StringAgg
returns an empty string instead of None
. This behavior is
deprecated and will be removed in Django 5.0. If you need it,
explicitly set default
to Value('')
.
y
and x
¶The arguments y
and x
for all these functions can be the name of a
field or an expression returning a numeric data. Both are required.
Corr
¶CovarPop
¶Returns the population covariance as a float
, or default
if there
aren’t any matching rows.
Optional. By default CovarPop
returns the general population
covariance. However, if sample=True
, the return value will be the
sample population covariance.
RegrAvgX
¶RegrAvgY
¶RegrCount
¶RegrIntercept
¶RegrR2
¶RegrSlope
¶RegrSXX
¶RegrSXY
¶RegrSYY
¶We will use this example table:
| FIELD1 | FIELD2 | FIELD3 |
|--------|--------|--------|
| foo | 1 | 13 |
| bar | 2 | (null) |
| test | 3 | 13 |
Here’s some examples of some of the general-purpose aggregation functions:
>>> TestModel.objects.aggregate(result=StringAgg("field1", delimiter=";"))
{'result': 'foo;bar;test'}
>>> TestModel.objects.aggregate(result=ArrayAgg("field2"))
{'result': [1, 2, 3]}
>>> TestModel.objects.aggregate(result=ArrayAgg("field1"))
{'result': ['foo', 'bar', 'test']}
The next example shows the usage of statistical aggregate functions. The underlying math will be not described (you can read about this, for example, at wikipedia):
>>> TestModel.objects.aggregate(count=RegrCount(y="field3", x="field2"))
{'count': 2}
>>> TestModel.objects.aggregate(
... avgx=RegrAvgX(y="field3", x="field2"), avgy=RegrAvgY(y="field3", x="field2")
... )
{'avgx': 2, 'avgy': 13}
Sep 04, 2024