SqlAlchemy: query array of objects in JSONB field

Question

I want to know how to do exactly this, except in SQLAlchemy instead of pure SQL syntax.

So just to reiterate from that question, you have a table with column of type jsonb, let's call it data. In this scenario data will always be an array of objects. I would like to query for rows where data contains an object with a key set to a specific value.

The answer I've linked here gives a great explanation for how to do it in SQL language, how do I do the same thing in SQLAlchemy?

Answer 1

The problem is that the jsonb key remains a jsonb object. This is simply solved by using the astext syntax.

So if your jsonb column is called data in the MyObject table:

object = session.query(
    MyObject
).\
    filter(MyObject.data['key'].astext=='required_key').\
    all()