使用django对postgres ArrayField进行不区分大小写的搜索

In the Django documentation for ArrayField, it lists a contains field lookup. However, no icontains (case insensitive lookup - many other fields have it) lookup is present in ArrayField.

在ArrayField的Django文档中，它列出了一个包含字段查找。但是，ArrayField中不存在任何icontains（不区分大小写的查找 - 许多其他字段都有）查找。

I need a case-insensitive lookup function for ArrayField, similar to the pre-existing contains lookup.

我需要一个对ArrayField不区分大小写的查找函数，类似于预先存在的包含查找。

2 个解决方案

#1

icontains, iexact are only applied on string type:

icontains，iexact仅适用于字符串类型：

my_array_field__contains=['H']

check if ['H'] is included in my_array_field

检查my_array_field中是否包含['H']

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

因为它检查第一个元素是否包含H或h

#2

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

您可以使用icontains进行不区分大小写的查找，省略您将用于包含的方括号：

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:

这是有效的，因为Postgres将ArrayField转换为文本，然后检查'my_substring'作为数组文本表示的子字符串。您可以通过检查生成的查询来查看：

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.

这在Postgres 10.1中对我有用。

#1