Sqlalchemy json column - how to preform a contains query
<p>I have the following table in mysql(5.7.12):</p>

<pre><code>class Story(db.Model):
    sections_ids = Column(JSON, nullable=False, default=[])
</code></pre>

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

<pre><code>stories = session.query(Story).filter(
        X in Story.sections_ids
    ).all()
</code></pre>

<p>but it throws:</p>

<pre><code>NotImplementedError: Operator 'contains' is not supported on this expression
</code></pre>

<p>Use <a href="https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-contains" rel="nofollow"><code>JSON_CONTAINS(json_doc, val[, path])</code></a>:</p>

<pre><code>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()
</code></pre>

<p>As you're searching an array at the top level, you do not need to give <em>path</em>.</p>

python mysql json sqlalchemy