PostgreSQL specific query expressions

These expressions are available from the django.contrib.postgres.expressions module.

ArraySubquery() expressions

class ArraySubquery(queryset)

New in Django 4.0.

ArraySubquery is a Subquery that uses the PostgreSQL ARRAY constructor to build a list of values from the queryset, which must use QuerySet.values() to return only a single column.

This class differs from ArrayAgg in the way that it does not act as an aggregate function and does not require an SQL GROUP BY clause to build the list of values.

For example, if you want to annotate all related books to an author as JSON objects:

>>> from django.db.models import OuterRef
>>> from django.db.models.functions import JSONObject
>>> from django.contrib.postgres.expressions import ArraySubquery
>>> books = Book.objects.filter(author=OuterRef('pk')).values(
...     json=JSONObject(title='title', pages='pages')
... )
>>> author = Author.objects.annotate(books=ArraySubquery(books)).first()
>>> author.books
[{'title': 'Solaris', 'pages': 204}, {'title': 'The Cyberiad', 'pages': 295}]