SQLAlchemy with Postgres: Property returning JSONElement instead of result

Question

Goal: Object has two attributes: First attribute points to a Python list backed by a JSON array. Second attribute points to a specific index in that first attribute's list.

Problem: Second attribute listed above currently doesn't work.

Background: SQLAlchemy attribute with a TypeDecorator derived from JSONB works as expected. When trying to index the attribute (extract the second value in the array, for instance), I get a JSONElement (derived from BinaryExpression) instead of an actual result.

Attributes in my class are defined as follows:

class MyClass...
    values_to_hold = Column(MyArr)

MyClass.second_val_in_arr = MyClass.values_to_hold[1]

second_val_in_arr returns a JSONElement instead of the result as expected. Explicitly adding astext (MyClass.values_to_hold[1].astext) does not help either.

If I set:

MyClass.second_val_in_arr = MyClass.values_to_hold

then second_val_in_arr returns the actual array as expected. But if I try to perform an index operation on the array (as above), then it suddenly returns a JSONElement.

Additional info:

MyArr is a TypeDecorator as below:

class MyArr(TypeDecorator):
    impl = JSONB

    def coerce_compared_value(self, op, value):
        return self.impl.coerce_compared_value(op, value)

    def process_result_value(self, value, dialect):
        if value:
            return list(value)
        else:
            return list()

(Note that coerce_compared_value is explicitly overridden because of the special logic required for JSON as per the docs).

Ilja Everilä · Accepted Answer · 2017-08-03 10:14:19Z

1

The assignment

MyClass.second_val_in_arr = MyClass.values_to_hold[1]

will just add the JSONElement as a regular attribute to the class. SQLAlchemy will not treat it in any special way, so instances of that class will look the attribute up from the class, resulting in the original JSONElement. Instead define a hybrid attribute:

from sqlalchemy.ext.hybrid import hybrid_property

class MyClass(...):

    values_to_hold = Column(MyArr)

    @hybrid_property
    def second_val_in_arr(self):
        return self.values_to_hold[1]

The hybrid will act as JSONElement/BinaryExpression in query context, when accessed through the class. It will return the 2nd item in values_to_hold when accessed on an instance of the class.

answered Aug 3, 2017 at 10:14

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

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

Collectives™ on Stack Overflow

SQLAlchemy with Postgres: Property returning JSONElement instead of result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related