#StackBounty: #python #json #postgresql #orm #sqlalchemy SQLALchemy: Query a single key or subset of keys of a Postgres JSONB column

Bounty: 50

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!

Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.