11

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)
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
SteveL
  • 3,331
  • 4
  • 32
  • 57

1 Answers1

4

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)

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
serv-inc
  • 35,772
  • 9
  • 166
  • 188
  • The postgreSQL way doesnt seem the very good, what if I have more info inside the telephone ? like "telephones": {"telephone": "54435345","contact_hours":"9-5"}, it will not match. The MongoDB and the MySQL is just a nogo since the whole project is in postgres. – SteveL Jul 03 '17 at 07:10
  • @SteveL: Sorry for not being able to help you further. If you would like me to offer the bounty on this question, just say so :-) – serv-inc Jul 03 '17 at 13:06
  • 1
    @SteveL Oldies goldies, but: the additional keys would not affect the *containment* check (not exact match/equality), which as its name indicates checks if the given JSON path/value is contained in the `jsonb` on the left. In addition since `"telephones"` contains a list, the JSON path/value should also contain the list, as in `{"telephones": [{"telephone": "54435345"}]}`. – Ilja Everilä Dec 02 '19 at 08:27