sqlalchemy, filter a json column containing an array [duplicate]

Question

I have a table with a json column "contact_info", the structure of this column is usually this:

{
    "telephones":[
        {"telephone":54435345,"type":"landline"},
        {"telephone":694823747,"type":"mobile"},
    ]
}

I want to find all the rows that have a specific telephone, The only thing I found around json arrays in sqlalchemy is something like this:

Table.contact_info["telephones"][0]["telephone"].astext.ilike(mask)

But this searches only the 0th element.

Currently my stupid solution is to convert the "telephones" into text and do an ilike, but this is wrong of course...

Table._contact_info["telephones"].astext.ilike(mask)

Answer 1

In PostgreSQL with JSONB you can use the containment check:

dict = {"telephones": [{"telephone": "54435345"}]}
user = cls.query.filter(your_table.contact_info.contains(dict)).first()

In MySQL it might be possible to use func.json_contains:

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

(you need to adapt and try it out, certainly the MySQL way, but probably also the PostgreSQL one)