Sqlalchemy json column - how to execute a preliminary query a contains a query

Question

I have the following table in mysql (5.7.12):

class Story(db.Model):
    sections_ids = Column(JSON, nullable=False, default=[])

sections_ids is basically a list of integers [1, 2, ..., n]. I need to get all the lines where section_ids contains X. I tried the following:

stories = session.query(Story).filter(
        X in Story.sections_ids
    ).all()

but he throws:

NotImplementedError: Operator 'contains' is not supported on this expression

Answer 1

Use JSON_CONTAINS(json_doc, val[, path]):

from sqlalchemy import func

# JSON_CONTAINS returns 0 or 1, not found or found. Not sure if MySQL
# likes integer values in WHERE, added == 1 just to be safe
session.query(Story).filter(func.json_contains(Story.section_ids, X) == 1).all()

When you search for an array at the top level, you do not need to specify a path.

Answer 2

For those who get here but use PostgreSQL instead: your fields should be of type sqlalchemy.dialects.postgresql.JSONB(and not sqlalchemy_utils.JSONType) -

Comparator, contains ( ).

:

Query(Mymodel).filter(MyModel.managers.comparator.contains(["[e-mail]"]))

( , JSON, )