Sql Alchemy can't cast jsonb to boolean

Question

I have the following value I'm pulling from a model, and trying to cast to a boolean. However, when I run the program I get an error stating `can't cast jsonb type to boolean. The value for the jsonb value here is a boolean, so why can't it cast it? What do I need to change?

The data value that I'm trying to get is the boolean out of {"unsubscribe" : "True"}

Here is the line that causes the error.

args.append(Customer.data['unsubscribed'].cast(sqlalchemy.Boolean) == "{}".format(True))

Here is the customer model

class Customer(Base):
    __tablename__ = 'customers'
    id = Column(UUID, primary_key=True, server_default='uuid_generate_v4()')
    phone_number = Column(String)
    data = Column(JSONB)
    created_at = Column(DateTime, server_default='NOW()')
    updated_at = Column(DateTime, server_default='NOW()')

    @property
    def agent_number(self):
        return self.data["agent"]["phoneNumber"]

    def __repr__(self):
        return '<Customer(id={}, phone_number={}, data={}, created_at={}, updated_at={})>'.format(
            self.id,
            self.phone_number,
            self.data,
            self.created_at,
            self.updated_at
        )

Answer 1

As far as PostgreSQL is concerned, the true is a jsonb type, not a SQL boolean type. You can't do

SELECT 'true'::jsonb::boolean;

You also can't do

SELECT '123'::jsonb::int;

You'll need to do some sort of conversion. One way is to convert to varchar using ->>:

SELECT (('{"unsubscribed": true}'::jsonb)->>'unsubscribed')::boolean;

Or, you can use jsonb_to_record:

SELECT unsubscribed FROM jsonb_to_record('{"unsubscribed": true}'::jsonb) AS o(unsubscribed boolean);

As far as SQLAlchemy is concerned, you can do

Customer.data['unsubscribed'].astext.cast(sqlalchemy.Boolean).is_(True)

Or, go the other way

Customer.data['unsubscribed'] == cast('true', JSONB)