CODEWITHSUNDEEP
pythonjsonpostgresqlormsqlalchemy
SmCaterpillar
SmCaterpillar

Reputation: 7020

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

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!

Upvotes: 4

Views: 2705

Answers (1)

Sebastian Kreft
Sebastian Kreft

Reputation: 8189

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.

Upvotes: 4

Related Questions