SQLAlchemy filter for rows that do not contain value in an array column

Question

I have 1001 rows in my table.

# 1001
print len(Session.query(MyTable).all())

One row has the string 'Recalled' in the info column, which is an array.

# 1
query = Session.query(MyTable)
query = query.filter(MyTable.info.contains(['Recalled']))
print len(query.all())

But when I negate the filter, I get 0 results, instead of 1000 like I expected.

# 0
query = Session.query(MyTable)
query = query.filter(~MyTable.info.contains(['Recalled']))
print len(query.all())

Why isn't this working?

Answer 1

This is just NULL values being tricky. You should explicitly include them, like

from sqlalchemy.sql import or_
query = Session.query(MyTable)
query = query.filter(or_(MyTable.info == None, ~MyTable.info.contains(['Recalled'])))
print len(query.all())