Shoham Shoham - 1 year ago 265
JSON Question

Sqlalchemy json column - how to preform a contains query

I have the following table in mysql(5.7.12):

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

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

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

but it throws:

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

Answer Source

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()

As you're searching an array at the top level, you do not need to give path.