Sqlalchemy, filter json column containing array

Question

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

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

I want to find all rows with a specific phone. The only thing I found in json arrays in sqlalchemy is something like this:

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

But this only searches for the 0th element .

Currently, my stupid solution is to convert the “phones” to text and do it ilike, but it’s wrong, of course ...

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

Answer 1

It depends on the database. Try one of the following:

In MongoDB (yes, it is not sql, but for JSON it is easy and intuitive)

db.contact_info.find({"telephones.telephone": {"$in": [54435345]}})

PostgreSQL JSONB -

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

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

( , , MySQL, , , PostgreSQL)