SQLAlchemy - Postgres: using with_entities for querying JSON element

Question

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.

Shouldn't the ['rule'] part be on the result, not in the with_entities field? — Paul Becotte
– Paul Becotte, Commented Jul 27, 2016 at 18:34
@Paul Becotte: I do not think so. Otherwise, the whole purpose of accessing JSON fields from the query is lost. — jazzblue
– jazzblue, Commented Jul 27, 2016 at 18:42
It works for me. (session.add(MyTable(data={"rule": "foo"})); session.flush(); session.query(MyTable)...) Perhaps you don't have any rows in your table? — univerio
– univerio, Commented Jul 27, 2016 at 19:48
@univerio: I do, in fact when I query for another non-JSON column I get results. Could you post what your my_query[0] look like? — jazzblue
– jazzblue, Commented Jul 27, 2016 at 19:52
(u'foo',). When you say the result is empty, my_query is the empty list? — univerio
– univerio, Commented Jul 27, 2016 at 20:04

jazzblue · Accepted Answer · 2016-07-27 22:11:34Z

1

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

answered Jul 27, 2016 at 20:34

jazzblue

2,4375 gold badges42 silver badges63 bronze badges

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

1 Answer 1