SqlAlchemy: query array of objects in JSONB field [duplicate]

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?

Joost Döbken · Accepted Answer · 2016-04-01 09:22:09Z

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

answered Apr 1, 2016 at 9:22

Joost Döbken

4,1476 gold badges50 silver badges96 bronze badges

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

Collectives™ on Stack Overflow

SqlAlchemy: query array of objects in JSONB field [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related