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

2 files changed, 52 insertions, 1 deletions

diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index ef1624fa0..215c09e60 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -1728,7 +1728,55 @@ class JSON(Indexable, TypeEngine):
 
     Index operations return an expression object whose type defaults to
     :class:`.JSON` by default, so that further JSON-oriented instructions
-    may be called upon the result type.
+    may be called upon the result type.   Note that there are backend-specific
+    idiosyncracies here, including that the Postgresql database does not generally
+    compare a "json" to a "json" structure without type casts.  These idiosyncracies
+    can be accommodated in a backend-neutral way by by making explicit use
+    of the :func:`.cast` and :func:`.type_coerce` constructs.
+    Comparison of specific index elements of a :class:`.JSON` object
+    to other objects work best if the **left hand side is CAST to a string**
+    and the **right hand side is rendered as a json string**; a future SQLAlchemy
+    feature such as a generic "astext" modifier may simplify this at some point:
+
+    * **Compare an element of a JSON structure to a string**::
+
+        from sqlalchemy import cast, type_coerce
+        from sqlalchemy import String, JSON
+
+        cast(
+            data_table.c.data['some_key'], String
+        ) == '"some_value"'
+
+        cast(
+            data_table.c.data['some_key'], String
+        ) == type_coerce("some_value", JSON)
+
+    * **Compare an element of a JSON structure to an integer**::
+
+        from sqlalchemy import cast, type_coerce
+        from sqlalchemy import String, JSON
+
+        cast(data_table.c.data['some_key'], String) == '55'
+
+        cast(
+            data_table.c.data['some_key'], String
+        ) == type_coerce(55, JSON)
+
+    * **Compare an element of a JSON structure to some other JSON structure** - note
+      that Python dictionaries are typically not ordered so care should be taken
+      here to assert that the JSON structures are identical::
+
+        from sqlalchemy import cast, type_coerce
+        from sqlalchemy import String, JSON
+        import json
+
+        cast(
+            data_table.c.data['some_key'], String
+        ) == json.dumps({"foo": "bar"})
+
+        cast(
+            data_table.c.data['some_key'], String
+        ) == type_coerce({"foo": "bar"}, JSON)
 
     The :class:`.JSON` type, when used with the SQLAlchemy ORM, does not
     detect in-place mutations to the structure.  In order to detect these, the
diff --git a/lib/sqlalchemy/sql/type_api.py b/lib/sqlalchemy/sql/type_api.py
index 98ede4e66..bb9de20fc 100644
--- a/lib/sqlalchemy/sql/type_api.py
+++ b/lib/sqlalchemy/sql/type_api.py
@@ -1213,6 +1213,9 @@ class Variant(TypeDecorator):
         self.impl = base
         self.mapping = mapping
 
+    def coerce_compared_value(self, operator, value):
+        return self.impl.coerce_compared_value(operator, value)
+
     def load_dialect_impl(self, dialect):
         if dialect.name in self.mapping:
             return self.mapping[dialect.name]