sqlalchemy, filter a json column containing an array

Viewed 6682

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)
1 Answers
Related