Flask SQLAlchemy select child object - return JSONB column

Question

I have a class with a one-many relationship. I would like to return all the parent’s children in the relationship ; specifically I’d like to return all the JSONB objects in my children tables.

These are my class:

class Parent(db.Model):
    __tablename__ = ‘parent220416'
    id = db.Column(db.Integer, primary_key=True)
    children = db.relationship(‘Child’, backref=‘Parent’, lazy='dynamic')

class Child(db.Model):
    __tablename__ = ‘child220416'
    id = db.Column(db.Integer, primary_key=True)
    parentid = db.Column(db.Integer, db.ForeignKey('words220416.id'))
    data = db.Column(JSONB)

Then with Flask Restful, I’m trying to select all the child like this:

class ParentChild(Resource):
    def get(self, id):
           result = db.session.query(Parent).get(id)
           result_child = result.children
           return {'child': result_child}

There is an error:

raise TypeError(repr(o) + " is not JSON serializable")
TypeError: <sqlalchemy.orm.dynamic.AppenderBaseQuery object at 0x106178da0> is not JSON serializable

Answer 1

If you want to get all of the data objects for each Child of the Parent. you could do the following query:

result_child = db.session.query(Child.data).filter(Child.parentid == id).all()

If you want to use the children relationship, it is possible to iterate over it:

result_child = [child.data for child in result.children]