id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
28908	Array specific lookups do not work with array aggregates	Jaap Roes	Sergey Fedoseev	"It's not possible to use array lookups on an array aggregate  (`django.contrib.postgres.aggregates.ArrayAgg`). 

Given these models:

{{{#!python
import uuid

from django.contrib.postgres.fields import ArrayField
from django.db import models

class Publisher(models.Model):
    name = models.CharField(max_length=100, unique=True)

class Author(models.Model):
    id = models.UUIDField(default=uuid.uuid4, primary_key=True)
    name = models.CharField(max_length=100)

class Genre(models.Model):
    code = models.DecimalField(max_digits=3, decimal_places=1, primary_key=True)
    name = models.CharField(max_length=100)

class Book(models.Model):
    title = models.CharField(max_length=100)
    publishers = models.ManyToManyField(Publisher)
    authors = models.ManyToManyField(Author)
    genres = models.ManyToManyField(Genre)

class Novel(models.Model):
    title = models.CharField(max_length=100)
    publishers = ArrayField(models.CharField(max_length=100))
    authors = ArrayField(models.UUIDField())
    genres = ArrayField(models.DecimalField(max_digits=3, decimal_places=1))
}}}

These tests pass:

{{{#!python
from django.contrib.postgres.aggregates import ArrayAgg
from django.test import TestCase

from .models import Author, Book, Genre, Novel, Publisher

class BaseArrayTestCase:
    def setUp(self):
        self.author = Author.objects.create(name='Charles Dickens')
        self.publisher = Publisher.objects.create(name='Chapman & Hall')
        self.genre = Genre.objects.create(code='1.0', name='Novella')

class TestNovelArrayField(BaseArrayTestCase, TestCase):
    def setUp(self):
        super().setUp()
        self.novel = Novel.objects.create(
            authors=[self.author.id],
            publishers=[self.publisher.name],
            genres=[self.genre.code],
            title='A Christmas Carol')

    def test_overlap(self):
        qs = Novel.objects.filter(authors__overlap=[self.author.id])
        # SELECT * FROM novel WHERE novel.authors && '{b1824cde-011d-46ec-80f9-8139a20bdacb}'::uuid[];
        self.assertEqual(qs.get(), self.novel)

    def test_contains(self):
        qs = Novel.objects.filter(genres__contains=[self.genre.code])
        # SELECT * FROM novel WHERE novel.genres @> '{1.0}'::numeric(3, 1)[];
        self.assertEqual(qs.get(), self.novel)

    def test_contained_by(self):
        qs = Novel.objects.filter(publishers__contained_by=[self.publisher.name])
        # SELECT * FROM novel WHERE novel.publishers <@ '{Chapman & Hall}'::varchar(100)[];
        self.assertEqual(qs.get(), self.novel)

    def test_len(self):
        qs = Novel.objects.filter(authors__len=1)
        # SELECT * FROM novel WHERE array_length(novel.authors, 1) = 1;
        self.assertEqual(qs.get(), self.novel)
}}}

While these tests fail:

{{{#!python
class TestBookArrayAgg(BaseArrayTestCase, TestCase):
    def setUp(self):
        super().setUp()
        self.book = Book.objects.create(title='A Christmas Carol')
        self.book.authors.set([self.author])
        self.book.publishers.set([self.publisher])
        self.book.genres.set([self.genre])

    def test_overlap(self):
        qs = Book.objects.annotate(author_ids=ArrayAgg('authors__id'))
        qs = qs.filter(author_ids__overlap=[self.author.id])
        # Expected:
        #   SELECT book.*, ARRAY_AGG(book_authors.author_id) AS author_ids
        #   FROM book
        #   LEFT OUTER JOIN book_authors ON (book.id = book_authors.book_id)
        #   GROUP BY book.id
        #   HAVING ARRAY_AGG(book_authors.author_id) && '{b1824cde-011d-46ec-80f9-8139a20bdacb}'::uuid[];

        # Actual:
        #   Unsupported lookup 'overlap' for UUIDField or join on the field not permitted.
        self.assertEqual(qs.get(), self.book)

    def test_contains(self):
        qs = Book.objects.annotate(genre_codes=ArrayAgg('genres__code'))
        qs = qs.filter(genre_codes__contains=[self.genre.code])
        # Expected:
        #   SELECT book.*, ARRAY_AGG(book_genres.genre_id) AS genre_codes
        #   FROM book
        #   LEFT OUTER JOIN book_genres ON (book.id = book_genres.book_id)
        #   GROUP BY book.id
        #   HAVING ARRAY_AGG(book_genres.genre_id) @> '{1.0}'::numeric(3, 1)[];

        # Actual:
        #   SELECT
        #     ...
        #   HAVING ARRAY_AGG(book_genres.genre_id)::text LIKE '%[""1.0""]%';
        self.assertEqual(qs.get(), self.book)

    def test_contained_by(self):
        qs = Book.objects.annotate(publisher_names=ArrayAgg('publishers__name'))
        qs = qs.filter(publisher_names__contained_by=[self.publisher.name])
        # Expected:
        #   SELECT book.*, ARRAY_AGG(publisher.name) AS publisher_names
        #   FROM book
        #   LEFT OUTER JOIN book_publishers ON (book.id = book_publishers.book_id)
        #   LEFT OUTER JOIN publisher ON (book_publishers.publisher_id = publisher.id)
        #   GROUP BY book.id
        #   HAVING ARRAY_AGG(publisher.name) <@ '{Chapman & Hall}'::varchar(100)[];

        # Actual:
        #   Unsupported lookup 'contained_by' for CharField or join on the field not permitted.
        self.assertEqual(qs.get(), self.book)

    def test_len(self):
        qs = Book.objects.annotate(author_names=ArrayAgg('authors__name'))
        qs = qs.filter(author_names__len=1)
        # Expected:
        #  SELECT book.*, ARRAY_AGG(author.name) AS author_names
        #  FROM book
        #  LEFT OUTER JOIN book_authors ON (book.id = book_authors.book_id)
        #  LEFT OUTER JOIN author ON (book_authors.author_id = author.id)
        #  GROUP BY book.id
        #  HAVING array_length(ARRAY_AGG(author.name), 1) = 1;

        # Actual:
        #  Unsupported lookup 'len' for CharField or join on the field not permitted.
        self.assertEqual(qs.get(), self.book)
}}}"	New feature	closed	Database layer (models, ORM)	dev	Normal	fixed	ArrayAgg ArrayField lookup	Sergey Fedoseev	Accepted	1	0	0	0	0	0