Exclude null values from Django's ArrayAgg

Question

I'm using Django's postgres-specific ArrayAgg aggregator. It works fine but when the list is empty I get [None] instead of []. Is there any way to filter these null values out? I've tried to pass a filter argument to ArrayAgg but it didn't work. Here's a simplified example of my setup:

class Image(models.Model):
    # ...

class Reporter(models.Model):
    # ...

class Article(models.Model):
    reporter = models.ForeignKey(Reporter, related_name='articles')
    featured_image = models.ForeignKey(Image, related_name='articles')
    # ...

Then if I make this query:

reporter = Reporter.objects.annotate(
    article_images=ArrayAgg('articles__featured_image'),
    distinct=True
).first()

And the first reporter in the result set doesn't have any associated article, I get:

> reporter.article_images
[None]

I've tried to add a filter, but no luck:

Reporter.objects.annotate(
    article_images=ArrayAgg(
        'articles__featured_image',
        filter=Q(articles__featured_image__isnull=False)
    )
)

Biography

Answer 1

I've tried your syntax Q(field__isnull=False) and also ~Q(field=None); both work fine for me on Django 2.1.7 and PG 11.2: I get [] instead of [None].

In the Django shell, you can check the SQL query that Django generates for your queryset:

print(str(your_queryset.query))

The relevant SQL portion in my test was:

ARRAY_AGG("table"."field_id") 
    FILTER (WHERE "table"."field_id" IS NOT NULL)
    AS "agg"

Depending on the syntax variant used, you can also get the following, which however works out the same:

FILTER (WHERE NOT ("table"."field_id" IS NULL))

Coonskin

Answer 2

I also have the problem of getting [None] instead of [] even when using default=Value([]) in the ArrayAgg annotation. My dirty solution due to the lack of deeper knowledge in the django ORM is manipulating the original file .../site-packages/django/db/models/expressions.py:

class BaseExpression:

...

@cached_property
def convert_value(self):
    
    ...

    elif internal_type == "DecimalField":
        return lambda value, expression, connection: (
            None if value is None else Decimal(value)
        )
    elif internal_type == "ArrayField":
        return lambda value, expression, connection: (
            [] if value == [None] else value
        )
    return self._convert_value_noop

After this change every ArrayField will return [] instead of [None], what suits my needs, but i think there is a better solution out there.

After every update of django this code is overwritten so i use a file update.py for this:

with open('/venv/lib/python3.12/site-packages/django/db/models/e'
          'xpressions.py', 'r', encoding="utf-8") as f:
    old_data = f.read()
if not 'elif internal_type == "ArrayField":' in old_data:
    new_data = old_data.replace(
        '                None if value is None else Decimal(value)\n      '
        '      )', '                None if value is None else Decimal(val'
        'ue)\n            )\n        elif internal_type == "ArrayField":\n'
        '            return lambda value, expression, connection: (\n     '
        '           [] if value == [None] else value\n            )', 1)
    with open('/venv/lib/python3.12/site-packages/django/db/mode'
              'ls/expressions.py', 'w', encoding="utf-8") as f:
        f.write(new_data)

Anthropolatry