SQLALchemy: Query a single key or subset of keys of a Postgres JSONB column

Question

I have a Postgres table that has a JSONB column. How do I query data of this column without loading the whole column at once in SQLAlchemy?

Let's say the JSONB column myjsonb contains {'a': 1, 'b': 2, 'c': 3, ... 'z': 26}. I only want the value of 'a' and not all 26 values. How do I specify a query to do that?

For example,

query = session.query(MyTable).options(defer('myjsonb')).join(MyTable.myjsonb['a'])

does not work.

Any idea how I can only retrieve 'a'? And what happens if the key 'a' is not present? And how can I load multiple keys, let's say 'b' to 'f', but not all of them at once? Thanks!

Sebastian Kreft · Accepted Answer · 2019-07-12 15:43:02Z

5

+50

This is actually pretty straight forward, you just query for the keys you are interested as in:

query = session.query(MyTable.myjsonb['a'])

If you want multiple keys from the json object, lets say a and c, you do

query = session.query(MyTable.myjsonb['a'], MyTable.myjsonb['c'])

If any of the keys are not present, it will just return None for that row.

answered Jul 12, 2019 at 15:43

Sebastian Kreft

8,2393 gold badges29 silver badges45 bronze badges

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

2 Comments

SmCaterpillar Over a year ago

Unfortunately this doesn't work, I either get: NotImplementedError: Operator 'getitem' is not supported on this expression or AttributeError: 'Table' object has no attribute 'measurements' if I do MyTable.__table__.myjsonb['a']

SmCaterpillar Over a year ago

duh, I was using jsonfield to allow json in our dev sqlite db. Removing this and everything works as expected. Thanks!

Collectives™ on Stack Overflow

SQLALchemy: Query a single key or subset of keys of a Postgres JSONB column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related