2

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?

Ilja Everilä
  • 45,748
  • 6
  • 100
  • 105
J-bob
  • 7,492
  • 10
  • 43
  • 78

1 Answers1

0

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()
Joost Döbken
  • 2,870
  • 1
  • 28
  • 64