Performing PostgreSQL match using OR on a JSON field

Question

I have a model that looks like this

class Message(db.Model):

    content = db.Column(JSON)

I want to perform a search on that field which contain some JSON fields

normally when I something like this it works

Message.query.filter(Message.content['summary'].cast(Unicode).match(search_term))

Now I want to use or another field if the field summary doesn't exist or is empty

using either with or without casting fails with programming error of types needs to be boolean

Message.query.filter(Message.content['summary'] | Message.content['text+video'].match(search_term))

EzzatA · Accepted Answer · 2017-05-31 08:43:14Z

1

Thanks to Elmer on the #sqlalchemy IRC channel, the logic was not correct, that's how I should have performed the search

Message.query.filter(Message.content['summary'].cast(Unicode).match(search_term) | Message.content['text'].cast(Unicode).match(search_term))

People who are interested should also follow this link where there is a related question with a better answer SQLAlchemy Text Matching data inside JSON field with UTF-8

answered Nov 30, 2016 at 11:38

EzzatA

1142 silver badges11 bronze badges

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

1 Answer 1