sqlalchemy, filter a json column containing an array

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

Answers 1

This seems to depend on the database. Try one of the following

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

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

In PostgreSQL with JSONB maybe something like

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)

If You Enjoyed This, Take 5 Seconds To Share It

• Facebook
• Twitter
• Google+
• StumbleUpon
• Digg
• Delicious
• LinkedIn
• Reddit
• Technorati