QuerySet.extra use case. Unnest array and make it lower. and make it an array again.

[june]

I had used extra() for annotating Arrayfield with lower cases and filter out with another array intersection.

Below is what I used for

`

lower_value = [v.lower() for v in value]
numbers = Numeric.objects.extra(

where=[f"ARRAY(SELECT lower(unnest({array_field}))) && %s::text[]"],
params=[lower_value],

)

`

I need to unnest arrayfield first and make each elements lowercase, and make it an Array again.
and then need to check overlay with another array.

[Simon Charette]

There are multiple ways to achieve that without the usage of extra.

The most straightforward is to use RawSQL ​as mentioned in the docs right below the part that mentions using creating a ticket.

Numeric.objects.filter(
    RawSQL(
        f"ARRAY(SELECT lower(unnest({array_field}))) && %s::text[]",
        (lower_values,)
        output_field=BooleanField(),
    )
)

But you could also use a Func

class ArrayLower(Func):
    template = "ARRAY(SELECT lower(unnest(%(expressions)s)))"
    arity = 1
    output_field = ArrayField(TextField())

Numeric.objects.alias(
    array_field_lower=ArrayLower("array_field")
).filter(
    array_field_lower__overlap=lower_values
)

It would also be pretty straightforward to define an __ioverlap ​transform if you wanted to to encapsulate all of that logic

Numeric.objects.filter(
    array_field__ioverlap=value
)

In short, you don't need extra at all to achieve what you're after. Please refer to ​the expression documentation and familiarize yourself with RawSQL usage if you want to resort to raw sql usage.