Python SQLAlchemy and Postgres - How to search using substring in json value

Question

I am aware I can easily query a JSON value using this example query:

r = Books.query.filter(
  Books.nameofjsonfield['key1', 'key2'].astext.cast(Unicode) == 'exact_string_to_compare'
).all()

But how do I search for datasets matching a substring of the JSON value?

Answer 1

After converting the value to text it is a simple matter of using LIKE one way or the other, or any of the other fuzzier matching methods:

r = Books.query.\
    filter(Books.nameofjsonfield['key1', 'key2'].
           astext.
           cast(Unicode).
           contains('not_so_exact_string_to_find')).\
    all()