- changed milestone to 1.1.x
- changed title to variant() does not transfer expression behavior using the comparator correctly
- changed component to sql
variant() does not transfer expression behavior using the comparator correctly
Hi,
I found a different behaviour of the JSON column working with on MySQL and on PostgreSQL.
Somebody can say me if I did something wrong or it's a bug?
I run the query session.query(table).filter(table.js['hello'] == 'world').first()
, and:
-
with
sa.Column(sa.JSON().with_variant(postgresql.JSONB(), 'postgresql'),)
works on PostgreSQL but not with MySQL. -
with
sa.Column(sa.JSON())
doesn't work with PostgreSQL but works with MySQL.
p.s. I attached a fully example ready-to-run.
Comments (4)
-
repo owner -
repo owner the Postgresql JSON needs a cast to compare JSON to string. Note that in JSON,
foo['bar']
returns a JSON value, not string. not sure if there's any solution to that coming anytime soon. -
repo owner here's how to compare JSON elements in a backend-agnostic way for now:
expr = sa.cast(table.js['hello'], sa.String) == sa.type_coerce("world", sa.JSON) result = session.query(table).filter(expr).first()
adding this to the docs
-
repo owner - changed status to resolved
Ensure Variant passes along impl right-hand type
Fixed issue in :class:
.Variant
where the "right hand coercion" logic, inherited from :class:.TypeDecorator
, would coerce the right-hand side into the :class:.Variant
itself, rather than what the default type for the :class:.Variant
would do. In the case of :class:.Variant
, we want the type to act mostly like the base type so the default logic of :class:.TypeDecorator
is now overridden to fall back to the underlying wrapped type's logic. Is mostly relevant for JSON at the moment.This patch additionally adds documentation and basic tests to allow for backend-agnostic comparison of JSON index elements to other objects. A future version should attempt to improve upon this by providing "astext", "asint" types of operators.
Change-Id: I7b7b45d604a4ae8d1dc236a5a1248695aab5232e Fixes:
#3859→ <<cset df9b6492e5ca>>
- Log in to comment