CODEWITHSUNDEEP
pythonmysqlsqlalchemy
DorAyalon
DorAyalon

Reputation: 233

Python SqlAlchemy + MySql filtering by a JSON column data

I have a table Movies with a JSON column, defined as:

movie_info = Column('movie_info', JsonEncodedDict)

Where JsonEncodedDict:

class JsonEncodedDict(TypeDecorator):
    impl = Text

    def process_bind_param(self, value, dialect):
        if value is None:
            return '{}'
        else:
            return json.dumps(value)

    def process_result_value(self, value, dialect):
        if value is None:
            return {}
        else:
            return json.loads(value)

Inserting works as expected. My problem starts when I want to query data and perform filtering on my column. Suppose I have a record, in which movie_info column is:

{
    "title": "The Movie - 3",
    "review": "Best movie I have ever seen"
}

I would like to query my table and get all records where review contains 'best'

I have tried things like

session.query(Movies).filter('best' in Movies.movie_info['review']).all()

session.query(Movie).filter('best' in func.json_compare(Movies.movie_info['review'])).all()

and several more queries but nothing works. Any idea would be appreciated

Upvotes: 2

Views: 5180

Answers (1)

DorAyalon
DorAyalon

Reputation: 233

That was quick but I found a solution. I'm not deleting this question, as I've yet to find any answer online so I believe this will be helpful to others.

Using json_extract instead of json_contains:

session.query(Movies).filter(func.json_extract(Movies.movie_info, '$.review').ilike('%best%')).all()

Upvotes: 7

Related Questions