Incorrect processing of has_key parameter on JSONB columns with custom types

[Arnout van Meer]

In our codebase we have a custom type based on JSONB and noticed has_key wasn't working. This seems to be due to parameter to has_key getting run through the bind processor which results in unintended quoting of the parameter string.

Repro case, tested with 1.0.12:

from sqlalchemy import create_engine, Column, Table, MetaData, select, types
from sqlalchemy.dialects.postgresql import JSONB


class CustomJSONBType(types.TypeDecorator):
    impl = JSONB


e = create_engine("postgresql://localhost/jsonb_test", echo=True)
c = e.connect()
t = c.begin()

# this works
table = Table('jsonb_test', MetaData(), Column('data', JSONB))
table.create(c)

c.execute(table.insert(), [{"data": {"x": 2}}])

assert c.scalar(select([table]).where(table.c.data.has_key('x')))

# this doesn't work
table = Table('custom_jsonb_test', MetaData(), Column('data', CustomJSONBType))
table.create(c)

c.execute(table.insert(), [{"data": {"x": 2}}])

assert c.scalar(select([table]).where(table.c.data.has_key('x')))

SQL output in working case:

INFO sqlalchemy.engine.base.Engine SELECT jsonb_test.data 
FROM jsonb_test 
WHERE jsonb_test.data ? %(data_1)s
INFO sqlalchemy.engine.base.Engine {'data_1': 'x'}

In broken case:

INFO sqlalchemy.engine.base.Engine SELECT custom_jsonb_test.data 
FROM custom_jsonb_test 
WHERE custom_jsonb_test.data ? %(data_1)s
INFO sqlalchemy.engine.base.Engine {'data_1': '"x"'}

[Mike Bayer]

that definitely looks like it's doing the wrong thing! I'll have a look.

[Mike Bayer]

mmmmm nope, this is a caveat, but we have a big red warning for it here:

http://docs.sqlalchemy.org/en/rel_1_0/core/custom_types.html?highlight=typedec#sqlalchemy.types.TypeDecorator

Warning

Note that the behavior of coerce_compared_value is not inherited by default from that of the base type. If the TypeDecorator is augmenting a type that requires special logic for certain types of operators, this method must be overridden. A key example is when decorating the postgresql.JSON and postgresql.JSONB types; the default rules of TypeEngine.coerce_compared_value() should be used in order to deal with operators like index operations:

class MyJsonType(TypeDecorator):
    impl = postgresql.JSON

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

Without the above step, index operations such as mycol['foo'] will cause the index value 'foo' to be JSON encoded.

the above method will fix your example, thanks!