PostgreSQL specific aggregation functions¶
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]}
General-purpose aggregation functions¶
ArrayAgg
¶
-
class
ArrayAgg
(expression, distinct=False, filter=None, default=None, ordering=(), **extra)¶ Returns a list of values, including nulls, concatenated into an array, or
default
if there are no values.-
distinct
¶ An optional boolean argument that determines if array values will be distinct. Defaults to
False
.
-
ordering
¶ 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()
Changed in Django 5.0:In older versions, if there are no rows and
default
is not provided,ArrayAgg
returned an empty list instead ofNone
. If you need it, explicitly setdefault
toValue([])
.-
BitAnd
¶
-
class
BitAnd
(expression, filter=None, default=None, **extra)¶ Returns an
int
of the bitwiseAND
of all non-null input values, ordefault
if all values are null.
BitOr
¶
-
class
BitOr
(expression, filter=None, default=None, **extra)¶ Returns an
int
of the bitwiseOR
of all non-null input values, ordefault
if all values are null.
BitXor
¶
-
class
BitXor
(expression, filter=None, default=None, **extra)¶ Returns an
int
of the bitwiseXOR
of all non-null input values, ordefault
if all values are null. It requires PostgreSQL 14+.
BoolAnd
¶
-
class
BoolAnd
(expression, filter=None, default=None, **extra)¶ Returns
True
, if all input values are true,default
if all values are null or if there are no values, otherwiseFalse
.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
¶
-
class
BoolOr
(expression, filter=None, default=None, **extra)¶ Returns
True
if at least one input value is true,default
if all values are null or if there are no values, otherwiseFalse
.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
¶
-
class
JSONBAgg
(expressions, distinct=False, filter=None, default=None, ordering=(), **extra)¶ Returns the input values as a
JSON
array, ordefault
if there are no values. You can query the result usingkey and index lookups
.-
distinct
¶ An optional boolean argument that determines if array values will be distinct. Defaults to
False
.
-
ordering
¶ 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} ]}]>
Changed in Django 5.0:In older versions, if there are no rows and
default
is not provided,JSONBAgg
returned an empty list instead ofNone
. If you need it, explicitly setdefault
toValue([])
.-
StringAgg
¶
-
class
StringAgg
(expression, delimiter, distinct=False, filter=None, default=None, ordering=())¶ Returns the input values concatenated into a string, separated by the
delimiter
string, ordefault
if there are no values.-
delimiter
¶ Required argument. Needs to be a string.
-
distinct
¶ An optional boolean argument that determines if concatenated values will be distinct. Defaults to
False
.
-
ordering
¶ 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' }]>
Changed in Django 5.0:In older versions, if there are no rows and
default
is not provided,StringAgg
returned an empty string instead ofNone
. If you need it, explicitly setdefault
toValue("")
.-
Aggregate functions for statistics¶
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
¶
-
class
Corr
(y, x, filter=None, default=None)¶ Returns the correlation coefficient as a
float
, ordefault
if there aren’t any matching rows.
CovarPop
¶
-
class
CovarPop
(y, x, sample=False, filter=None, default=None)¶ Returns the population covariance as a
float
, ordefault
if there aren’t any matching rows.-
sample
¶ Optional. By default
CovarPop
returns the general population covariance. However, ifsample=True
, the return value will be the sample population covariance.
-
RegrAvgX
¶
-
class
RegrAvgX
(y, x, filter=None, default=None)¶ Returns the average of the independent variable (
sum(x)/N
) as afloat
, ordefault
if there aren’t any matching rows.
RegrAvgY
¶
-
class
RegrAvgY
(y, x, filter=None, default=None)¶ Returns the average of the dependent variable (
sum(y)/N
) as afloat
, ordefault
if there aren’t any matching rows.
RegrCount
¶
-
class
RegrCount
(y, x, filter=None)¶ Returns an
int
of the number of input rows in which both expressions are not null.Note
The
default
argument is not supported.
RegrIntercept
¶
-
class
RegrIntercept
(y, x, filter=None, default=None)¶ Returns the y-intercept of the least-squares-fit linear equation determined by the
(x, y)
pairs as afloat
, ordefault
if there aren’t any matching rows.
RegrR2
¶
-
class
RegrR2
(y, x, filter=None, default=None)¶ Returns the square of the correlation coefficient as a
float
, ordefault
if there aren’t any matching rows.
RegrSlope
¶
-
class
RegrSlope
(y, x, filter=None, default=None)¶ Returns the slope of the least-squares-fit linear equation determined by the
(x, y)
pairs as afloat
, ordefault
if there aren’t any matching rows.
RegrSXX
¶
-
class
RegrSXX
(y, x, filter=None, default=None)¶ Returns
sum(x^2) - sum(x)^2/N
(“sum of squares” of the independent variable) as afloat
, ordefault
if there aren’t any matching rows.
Usage examples¶
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}