query filter on postgresql array column using array.contains()

Question

I have an array column (required of me, I know this is not typical convention) that I need to filter on in a query. I am trying to use contains(),

  db.session.query(table).filter((table.name.ilike
    ('%'+name+'%')),
    table.languages.contains(language)).limit(200)

But I keep getting this error:

    NotImplementedError: ARRAY.contains() not implemented for the base 
    ARRAY 
    type; please use the dialect-specific ARRAY type

I am new to Flask-Sqlalchemy so I am not sure how to use this correctly. Any help is much appreciated!

Answer 1

This Expection means you should use dialect-specific ARRAY type to instead the base ARRAY type. So in your models definition, Use sqlalchemy.dialects.postgresql.ARRAY to instead ARRAY

from sqlalchemy.dialects.postgresql import ARRAY
class table(db.Model):
    ...
    languages = db.Column(ARRAY) # notice this ARRAY import from sqlalchemy.dialects.postgresql

Now, you can use db.session.query(table).filter(table.languages.contains([language])).limit(200).