SQLAlchemy Multitype columns - Hybrid Property querying

Question

I have several tables in my PostgreSQL database that need to contain timeseries like information in an IoT-link context, that require several data types supported for the value of each point in the series, like this:

class Telemetry(Base):
    __tablename__ = "ts_kv"

    key = Column(Integer, primary_key=True)
    bool_v = Column(Boolean)
    str_v = Column(String)
    long_v = Column(BigInteger)
    dbl_v = Column(DOUBLE_PRECISION)
    json_v = Column(JSON)
    ts = Column(BigInteger, primary_key=True)
    entity_id = Column(UUID(as_uuid=True), nullable=False, primary_key=True)

I need to parse an incoming model from a REST API that has a single value field which can be any of the above types. I can parse the input data type correctly into the corresponding column with no issues.

The problem arises when I try to query the table for a given value, without directly specifying the data type, I would need to achieve something like this:

data = session.query(Telemetry.ts, Telemetry.value).filter(Telemetry.value == MY_MULTITYPE_VALUE).all()

I've tried using hybrid properties and hybrid methods like below:

@hybrid_property
def value(self):
    return self.str_v if self.str_v else (
        self.dbl_v if self.dbl_v else (
            self.bool_v if self.bool_v else (
                self.json_v if self.json_v else self.long_v
            )
        )
    )


@value.expression
def value(self):
    return self.str_v if self.str_v else (
        self.dbl_v if self.dbl_v else (
            self.bool_v if self.bool_v else (
                self.json_v if self.json_v else self.long_v
            )
        )
    )

The first part of the operation works correctly, as if I query:

session.query(Telemetry.value).filter(ANY FILTER).all()

I do get correct values. However, querying like in the following example fails, because the expression for value evaluates always true for the first part of the conditional, IE: if self.str_v yields always True as it evals the COLUMN as existing, not the actual value for the row in that column. I've also tried using the func and not_/is_null operators with the exact same result.

a = session.query(Telemetry).filter(Telemetry.value == 55).first() #Always fails because long_v is behind in the expression evaluation.

How can I achieve this behaviour in the simplest fashion? I've looked into composite columns but couldn't figure out a way to properly handle the parsing/de-parsing.

Answer 1

As @rfkortekaas correctly pointed out in his comment, the expression needs to be SQL. And if/else don't qualify.

from sqlalchemy import case

@hybrid_property
def value(self):
    return self.str_v if self.str_v is not None else (
        self.long_v if self.long_v is not None else (
            self.dbl_v if self.dbl_v is not None else self.bool_v
        )
    )

@value.expression
def value(cls):
    return case(
        (cls.str_v.is_not(None), cls.str_v),
        (cls.dbl_v.is_not(None), cls.dbl_v),
        (cls.long_v.is_not(None), cls.long_v),
        else_=cls.bool_v
    )