Queries involving postgres Array fields can't include expressions as elements

[Matthew Wilkes]

Issue #22288 allows use of expressions in lookups that take iterables rather than single items directly. This allows, for example:

NullableIntegerArrayModel.objects.filter(field__in=[models.F('field')]),

i.e., filtering an ArrayField using __in with expressions that evaluate to other ArrayField references.

However, the following fails:

NullableIntegerArrayModel.objects.filter(field__in=[[models.F('id')]]),

i.e., building a new array that contains scalar expressions.

There are some tests, marked with expectedFailure, in postgres_tests.test_array: test_in_including_F_object and test_contained_by_including_F_object, that demonstrate this problem.

[PREMANAND]

Im unable to locate the postgres_tests.test_array: test_in_including_F_object and test_contained_by_including_F_object in the master branch.

Can you please point me to the correct location?

It would be great if you can build an example, so we both are on the same page.

[Tim Graham]

​https://github.com/django/django/blob/674e3fe13e5156344bfafbea59018b8837eb3044/tests/postgres_tests/test_array.py#L176-L208

[Hannes Ljungberg]

I've created a PR (​https://github.com/django/django/pull/13680) to fix the issue of allowing expressions on ArrayField lookups where the right-hand side is a list. Note that this PR does not fix the issue of expressions in nested lists, it should in my opinion be tracked as a separate ticket. I'm not really familiar with the process of splitting up tickets, would it make sense to create a new ticket for the nested expressions in the case of this PR being merged?

[Mariusz Felisiak <felisiak.mariusz@…>]

In 33403bf8:

Refs #27095 -- Allowed (non-nested) arrays containing expressions for ArrayField lookups.

[Mariusz Felisiak]

Nested arrays still don't work with expressions.

[GitHub <noreply@…>]

In d746f289:

Refs #27095 -- Fixed test_contained_by_including_F_object when run in reverse.

Tests should not rely on auto PKs.

Test regression in 33403bf80f635577a18426bc99c8a65e31fd8dfa.

[Ammar Abdur Raheman]

Claiming this and submitting a patch for the remaining nested-array case.

​PR

ArrayInLookup now builds an ARRAY[...] expression for nested arrays that contain expressions, reusing the builder extracted from ArrayRHSMixin, and casts it to the left-hand side type. The cast is needed so that an IN list holding both literal and expression arrays resolves to a single type; without it, field__in=[[1], [F("order")]] fails with operator does not exist: bigint[] = integer[]. test_in_including_F_object is no longer marked as an expected failure.

Tests: the full suite passes under SQLite, and postgres_tests, queries, expressions, lookup, annotations, aggregation, model_fields and custom_lookups pass under PostgreSQL 17.

The patch was prepared with assistance from Claude Code (Claude Opus 5); the pull request description records what it was used for.