SQLAlchemy, how to filter by empty array column?

Question

I have that kind of Model in SQLAlchemy:

class MyModel(Base):
    __tablename__ = 'my_models'
    name = Column(String(255), nullable=False)
    company_ids = Column(ARRAY(Integer), nullable=True)

I have intentionally created models with empty arrays and try to query them like this:

db.query(MyModel).filter(~MyModel.company_ids.any())

Which doesn't return anything.

Is it possible to filter models that have empty array field?

============UPDATE===============

I was able to query it by using this method:

db.query(MyModel).filter(MyModel.company_ids == '{}')

However I think it is only PostgreSQL specific answer

Mr.D · Accepted Answer · 2020-10-30 07:34:53Z

1

I was able to query it by using this method:

db.query(MyModel).filter(MyModel.company_ids == '{}')

However I think it is only PostgreSQL specific answer

answered Oct 30, 2020 at 7:34

Mr.D

7,96315 gold badges72 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kartikey rajvaidya · Accepted Answer · 2022-10-04 18:22:21Z

0

I think SQLAlchemy's way of doing this is

db.query(MyModel).filter(
 func.cardinality(MyModel.company_ids == 0)
)

answered Oct 4, 2022 at 18:22

kartikey rajvaidya

1011 silver badge4 bronze badges

Collectives™ on Stack Overflow

SQLAlchemy, how to filter by empty array column?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related