SQLAlchemy：如何过滤PgArray列类型？

Question

在纯PostgreSQL中，我们可以这样写：

SELECT * FROM my_table WHERE 10000 = ANY (array_field);

或者

SELECT * FROM my_table WHERE 10000 = ALL (array_field);

如何使用sqlalchemy而不是原始的SQL来完成相同的操作？

- Vitalii Ponomar

Answer 1

"

a = ANY(b_array)等同于aIN(b_array的元素)¹。

因此，您可以使用in_()方法。

我在使用PostgreSQL的所有年份中从未记得使用过a = ALL(b_array)。你用过吗？

"

---

如果你正在处理一个数组列，并想要测试它是否包含该列中的给定元素（或给定数组的所有元素），那么你可以使用PostgreSQL数组运算符@> （包含）或更合适的反向兄弟<@（被包含）。

数组运算符的优点在于它们可以在数组列上支持GIN索引（与ANY结构不同）。

你的SQL语句：

SELECT * FROM my_table WHERE 10000 = ANY (array_field);

（几乎）等价于

SELECT * FROM my_table WHERE 10000 <@ array_field;

根据SQLAlchemy手册中的教程，您可以使用任何运算符。我不是SQLAlchemy的专家。

If you have come across an operator which really isn’t available, you can always use the op() method; this generates whatever operator you need:

>>> print users.c.name.op('tiddlywinks')('foo') users.name tiddlywinks :name_1

加粗是我添加的。在SQLA中，您的语句可能如下所示：

s = select([my_table], array_field.op('@>')('ARRAY[10000]'))

或者使用PostgreSQL数组值的替代输入语法：

s = select([my_table], array_field.op('@>') (cast('{10000}', int[])))

---

¹ NULL处理存在微妙差别：

SELECT '{NULL}'::int[] <@ ... -- that's an array with a single NULL element

始终返回FALSE。

SELECT NULL IN (...)
SELECT NULL = ANY (...)
SELECT NULL::int[] <@ ...

始终返回NULL。

如果您不打算查询NULL值，则可以忽略此项。

- Erwin Brandstetter

---

好的，如何编写这样的查询：SELECT * FROM my_table WHERE 5 = ANY(array_field)？我理解in_()在这种情况下无法帮助... Session.query(MyTable).filter(MyTable.array_field.in_(?????)).all() - Vitalii Ponomar

@VitaliPonomar：抱歉，我删除了误导性信息。我进一步研究后发现了一个适当的解决方案。请考虑修改答案。 - Erwin Brandstetter

我有一个问题。如何编写 SELECT * FROM my_table WHERE [10000,200,600] = ANY (array_field); - 这意味着如果列 array_field 中存在 [10000,200,600] 中的任何一个值，则选择该行？ - Arup Rakshit

如果您想使用ALL数组比较器，请从Postgres方言中导入它(from sqlalchemy.dialects.postgresql import All)，并将其作为参数传递给Query对象上的having()方法：query.having(All(1, func.array_agg(Widget.id)))完整文档在此处 - Josh