SQLAlchemy PostgreSQL row_to_json relationship

Question

I wanted to know if it's possible to wrap the relationship using the server function jsonb or row_to_json instead of translatting the result in json using python.

When I retrieves the Documentation, I want to have the Image object as a json.

class Documentation(Base):

    __tablename__ = 'documentation'
    id = Column(UUID, server_default=text("uuid_generate_v4()"), primary_key=True)
    url =  Column(String, nullable=False)
    description = Column(String)
    name = Column(String)
    image_id = Column(UUID,ForeignKey(Image.id))
    image = relationship(Image,backref="image_data",lazy="subquery")


class Image(Base):

    __tablename__ = 'image'
    id = Column(UUID, server_default=text("uuid_generate_v4()"), primary_key=True)
    name = Column(VARCHAR(128), nullable=False)
    info = Column(JSONB)
    image = Column(BYTEA)

Exemple of the sql request that I want to translate using sqlalchemy:

select doc.*,
row_to_json(image.*) as image
from documentation doc
left join image on doc.image_id = image.id;

SQLAlchemy (current request)

q = Session.query(Documentation).all()

Is it possible to do it using sqlalchemy serverside (on postgresql) ? (for performance issues)

Thanks

Have you tried this? stackoverflow.com/questions/37010083/… — mad_
– mad_, Commented Jun 18, 2018 at 13:12

George Sibble · Accepted Answer · 2018-06-18 17:00:13Z

0

Right now these are two different tables. If you wanted to use jsonb instead of just translating into JSON as a property of Documentation, you'd need to move all of the image data into a new column under Documentation using one of the compatible json types. Since this is a 1:1 relationship, they belong in the same table anyways.

answered Jun 18, 2018 at 17:00

George Sibble

1731 silver badge6 bronze badges

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

Collectives™ on Stack Overflow

SQLAlchemy PostgreSQL row_to_json relationship

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