SQLAlchemy query for array containing any one of multiple values

Question

I figured I could find answer to this on the internet somewhere, but I seem to have missed it. You can query a table for all rows where an array column contains a specific value:

MyModel.query.filter(Mymodel.arrayField.contains(['someValue'])

And you can put in multiple values such that the array must contain all the specified values:

MyModel.query.filter(Mymodel.arrayField.contains(['someValue', 'anotherValue'])

But what about querying and where the array contains at-least one of the specified values. That is, a query that would return rows where arrayField contains 'someValue' or 'anotherValue', and maybe both but not necessarily both.

How would I do that?

Answer 1

The Postgresql concept here is overlapping arrays, implemented using the && operator:

test# SELECT ARRAY['a', 'x'] && ARRAY['a', 'b', 'c'] AS overlaps;
 overlaps 
══════════
 t
(1 row)

test# SELECT ARRAY['q', 'x'] && ARRAY['a', 'b', 'c'] AS overlaps;
 overlaps 
══════════
 f
(1 row)

SQLAlchemy provides an overlap method on the ARRAY type in the Postgresql dialect that implements &&.

An ORM query checking for columns containing at least one of 'a' and 'z' would look like

session.query(MyModel).filter(MyModel.array_field.overlap(['a', 'z']))

or SQLAlchemy 2.0-style:

select(MyModel).where(MyModel.array_field.overlap(['a', 'z']))