Comparator on Variant field

[Jiří Kunčar]

The comparator factory for Variant field doesn't lookup the engine specific implementation.

    @property
    def comparator_factory(self):
        """express comparison behavior in terms of the base type"""
        return self.impl.comparator_factory

I would expect something like:

    @property
    def comparator_factory(self):
        """express comparison behavior in terms of the base type"""
        return self.load_dialect_impl(FIXME).comparator_factory

There is a workaround which more or less works for me but it is not ideal:

    TestJSONType = sa.JSON().with_variant(
        postgresql.JSONB(), 'postgresql'
    ).with_variant(
        mysql.JSON(), 'mysql'
    )

    class TestJSON(db.Model):
        __tablename__ = 'test_json'

        pk = sa.Column(sa.Integer, primary_key=True)
        js = sa.Column(TestJSONType)

    from werkzeug.local import LocalProxy

    def variant_comparator(column):
        """Dialect specific comparator."""
        return column.type.load_dialect_impl(
            db.engine.dialect
        ).comparator_factory(column)

    TestJSON.js.comparator = LocalProxy(lambda: variant_comparator(TestJSON.js))

So later I would like to do: TestJSON.js['foo'].astext

[Mike Bayer]

By design this is not possible. You'll note here:

   @property
    def comparator_factory(self):
        """express comparison behavior in terms of the base type"""
        return self.load_dialect_impl(FIXME).comparator_factory

where would you get FIXME from? Here's an expression:

expr = some_json_column['foo']

There's no dialect there. If you envisioned some other way of this working please show me what you mean.

If you have a comparator that you'd like to take place, that type should be the leading type, not the variant.

[Jiří Kunčar]

Just an idea:

class DynamicVariant(sa.types.Variant):
    def __init__(self, base, mapping, dialect_proxy=None):
        ...

    def comparator_factory(self):
        # return factory of comparator proxy using dialect proxy

Usage with Flask(-SQLAlchemy):

TestJSONType = DynamicVariant(sa.JSON(), {
    'postgresql': postgresql.JSONB(),
    ...
}, dialect_proxy=LocalProxy(lambda: db.engine.dialect if has_app_context() else None))

[Mike Bayer]

the simple answer is that the comparator has to return objects that can be correctly interpreted by all the backends that you need.

What I actually need to know here is what operations you specifically need to work on the base JSON type that do not work when translated to JSONB.

[Jiří Kunčar]

We had a piece of code that tries to run specific JSONB query if available otherwise it fallbacks to for-loop or Elasticsearch.

if db.engine.name == 'postgresql':
   TestJSON.query.filter(
       TestJSON.js[('foo', 'bar')].astext / has_key / contains / ...
    ).all()

see https://bitbucket.org/zzzeek/sqlalchemy/src/7de0d1785335961ce0f723877ca7a8fd85b2c0ca/lib/sqlalchemy/dialects/postgresql/json.py?at=master&fileviewer=file-view-default#json.py-267:297 vs https://bitbucket.org/zzzeek/sqlalchemy/src/7de0d1785335961ce0f723877ca7a8fd85b2c0ca/lib/sqlalchemy/sql/sqltypes.py?at=master&fileviewer=file-view-default#sqltypes.py-1880:1881

[Mike Bayer]

yup here's how you do that, when you need to call explicit operators you need to cast to that type first:

from sqlalchemy import type_coerce

print type_coerce(A.data, JSONB())['bar'].astext == 'foo'

[Jiří Kunčar]

Thanks!