Case-insensitive search on a postgres ArrayField with django

You can do a case-insensitive look-up using icontains, by omitting the square brackets you would use for contains:

queryset = my_model.objects.filter(field_name__icontains='my_substring')

This works because Postgres casts the ArrayField to text and then checks for 'my_substring' as a substring of the array's text representation. You can see this by examining the resulting query:

print(queryset.query)

# raw SQL output:
'SELECT ... WHERE UPPER("my_model"."field_name"::text) LIKE UPPER(%my_substring%)

This worked for me in Postgres 10.1.

---

As an addendum, I should disclose that this approach caused a bug for my project. Say you want to check if the array field contains 'my_substring':

field_name__icontains='my_substring'

This will retrieve 'my_substring', but it will also retrieve 'foo_my_substring'. This has to do with the string casting mentioned above. Use at your own risk.

icontains, iexact are only applied on string type:

my_array_field__contains=['H'] 

check if ['H'] is included in my_array_field

But if you try the following, it will works:

my_array_field__0__icontains='H'

because it check if the first element contains H or h