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?

Ilja Everilä · Accepted Answer · 2019-11-26 21:43:19Z

0

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

edited Nov 26, 2019 at 21:43

answered Nov 26, 2019 at 21:37

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related