How do I use PostgreSQL's jsonb_to_record in SQLAlchemy?

Question

PostgreSQL has functions like jsonb_to_record or jsonb_populate_record which allow you convert data stored in a JSONB column to separate columns. however, I'm using SQLAlchemy and jsonb_to_record requires a predefined structure for the output (see example below from the PostgreSQL docs)

select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text)

Is there a way to use these functions from SQLAlchemy?

By SQLAlchemy, are we talking about the ORM or the core part? — Martijn Pieters
– Martijn Pieters, Commented Feb 28, 2020 at 21:29
I think this answer should help stackoverflow.com/a/25046952/4454340 — Vladyslav
– Vladyslav, Commented May 27, 2020 at 13:04

Thijs D · Accepted Answer · 2020-04-28 20:01:31Z

2

you can use any postgres function in SQLAlchemy with func like so:

from SQLAlchemy import func
session.query(func.json_to_record(Model.jsonb_field))

Do note that set-returning functions are a bit of a pain to deal with with SQLAlchemy, but if you want you can get it to work.

answered Apr 28, 2020 at 20:01

Thijs D

7926 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

How do I use PostgreSQL's jsonb_to_record in SQLAlchemy?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related