CODEWITHSUNDEEP
pythonjsonpostgresqlsqlalchemy
jazzblue
jazzblue

Reputation: 2437

SQLAlchemy - Postgres: using with_entities for querying JSON element

I am trying to query for a field in JSON column (Postgres):

class MyTable(Base):
    __tablename__ = 'my_table'

    data = Column(JSONB)

Query:

my_query = session.query(MyTable).limit(10).with_entities(MyTable.data['rule']).all()

I get no error, but the result is empty.

Even if I try with astext, same empty result:

my_query = session.query(MyTable).limit(10).with_entities(MyTable.data['rule'].astext).all()

Can I use with_entities in this case? What would be the work around? Thanks.

Upvotes: 1

Views: 671

Answers (1)

jazzblue
jazzblue

Reputation: 2437

The addition of label() method solved the issue for me:

my_query = session.query(MyTable).limit(10). \
        with_entities(MyTable.data['rule'].label('rule')).all()

Upvotes: 1

Related Questions