Syntax for row_to_json with sqlalchemy

Question

I would like to figure out how to use Postgres' (9.2) row_to_json with SqlAlchemy. However I haven't been able to come up with any working syntax.

details_foo_row_q = select([Foo.*]
    ).where(Foo.bar_id == Bar.id
).alias('details_foo_row_q')

details_foo_q = select([
    func.row_to_json(details_foo_row_q).label('details')
]).where(details_foo_row_q.c.bar_id == Bar.id
).alias('details_foo_q')

I would ideally like to not to have to type out each and every field from the table model if possible.

Got the answer from 'mn':

It should be something more like this:

details_foo_row_q = select([Foo]).where(Foo.bar_id == Bar.id).alias('details_foo_row_q')

details_foo_q = select([
    func.row_to_json(literal_column(details_foo_row_q.name)).label('details')
]).select_from(details_foo_row_q).where(
    details_foo_row_q.c.bar_id == Bar.id
).alias('details_foo_q')

Thank you mn, works great!

r-m-n · Accepted Answer · 2016-05-03 18:08:56Z

4

Your query generates an incorrect SQL

SELECT row_to_json(SELECT ... FROM foo) AS details
FROM (SELECT ... FROM foo) AS details_foo_row_q

It should be

SELECT row_to_json(details_foo_row_q) AS details
FROM (SELECT ... FROM foo) AS details_foo_row_q

You need to use select as literal_column

from sqlalchemy.sql.expression import literal_column

details_foo_q = select([
    func.row_to_json(literal_column(details_foo_row_q.name)).label('details')
]).select_from(details_foo_row_q).where(
    details_foo_row_q.c.bar_id == Bar.id
).alias('details_foo_q')

answered May 3, 2016 at 18:08

r-m-n

15.3k6 gold badges74 silver badges71 bronze badges

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

1 Comment

slashdottir Over a year ago

it works! I guess due to the 'name' variable as well as the literal_column

jakebrinkmann · Accepted Answer · 2021-12-23 14:26:00Z

2

Sounds like maybe there is a solution IFF you are using the latest SQLAlchemy:

# New in version 1.4.0b2.
>>> from sqlalchemy import table, column, func, select
>>> a = table( "a", column("id"), column("x"), column("y"))
>>> stmt = select(func.row_to_json(a.table_valued()))
>>> print(stmt)
SELECT row_to_json(a) AS row_to_json_1
FROM a

https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#table-types-passed-to-functions

answered Dec 23, 2021 at 14:26

jakebrinkmann

82511 silver badges23 bronze badges

Comments

Taras Bilobran · Accepted Answer · 2021-12-01 12:29:59Z

0

If other people still struggle with row_to_json function, I have good news for you. Let's imagine we have User class with fields email, id and we want to receive email and id fields as JSON. This can be done using json_build_object function:

from sqlalchemy import func

session.query(func.json_build_object("email", User.email, "id", User.id))

answered Dec 1, 2021 at 12:29

Taras Bilobran

11

Collectives™ on Stack Overflow

Syntax for row_to_json with sqlalchemy

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related