CODEWITHSUNDEEP
pythonjsonpostgresqlsqlalchemypostgresql-9.2
slashdottir
slashdottir

Reputation: 8536

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!

Upvotes: 4

Views: 2301

Answers (3)

jakebrinkmann
jakebrinkmann

Reputation: 805

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

Upvotes: 2

Taras Bilobran
Taras Bilobran

Reputation: 11

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

Upvotes: 0

r-m-n
r-m-n

Reputation: 15090

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

Upvotes: 3

Related Questions