Python SqlAlchemy + MySql filtering by a JSON column data

Question

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

Why use a TypeDecorator at all, if your MySQL back-end is using its native JSON type? Your updates to this field will be faithfully applied in the ORM, and you only need to flag a record change using sqlalchemy.orm.attributes.flag_modified prior to a commit(). — CodeMantle
– CodeMantle, Commented Aug 22, 2022 at 21:10

DorAyalon · Accepted Answer · 2019-08-27 20:47:11Z

7

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

answered Aug 27, 2019 at 20:47

DorAyalon

2433 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python SqlAlchemy + MySql filtering by a JSON column data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related