pythonjsonpostgresqlsqlalchemypostgresql-9.2

Syntax for row_to_json with sqlalchemy


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!


Solution

  • 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')