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?

Please take a look to this answer

lrnzcig
– lrnzcig

2015-09-24 09:18:27 +00:00
Commented Sep 24, 2015 at 9:18 — lrnzcig
– lrnzcig, Commented Sep 24, 2015 at 9:18

snakecharmerb · Accepted Answer · 2023-03-05 19:58:08Z

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']))

or if you prefer to be closer to the Postgres syntax:

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

Collectives™ on Stack Overflow

SQLAlchemy query for array containing any one of multiple values

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related