Add type accessors for JSON indexed/pathed element access

Added new accessors to expressions of type :class:`.JSON` to allow for
specific datatype access and comparison, covering strings, integers,
numeric, boolean elements.   This revises the documented approach of
CASTing to string when comparing values, instead adding specific
functionality into the PostgreSQL, SQlite, MySQL dialects to reliably
deliver these basic types in all cases.

The change also delivers a new feature to the test exclusions
system so that combinations and exclusions can be used together.

Fixes: #4276
Change-Id: Ica5a926c060feb40a0a7cd60b9d6e061d7825728

1 files changed, 42 insertions, 0 deletions

diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 06cfdc4b5..1e1835d1a 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -8,6 +8,7 @@ from sqlalchemy import Integer
 from sqlalchemy import LargeBinary
 from sqlalchemy import literal_column
 from sqlalchemy import not_
+from sqlalchemy import Numeric
 from sqlalchemy import or_
 from sqlalchemy import String
 from sqlalchemy import testing
@@ -712,6 +713,47 @@ class JSONIndexOpTest(fixtures.TestBase, testing.AssertsCompiledSQL):
         col = Column("x", MyOtherType())
         self.assert_compile(col[5], "x $$> :x_1", checkparams={"x_1": 5})
 
+    def _caster_combinations(fn):
+        return testing.combinations(
+            ("integer", Integer),
+            ("boolean", Boolean),
+            ("float", Numeric),
+            ("string", String),
+        )(fn)
+
+    @_caster_combinations
+    def test_cast_ops(self, caster, expected_type):
+        expr = Column("x", JSON)["foo"]
+
+        expr = getattr(expr, "as_%s" % caster)()
+        is_(expr.type._type_affinity, expected_type)
+
+    @_caster_combinations
+    def test_cast_ops_unsupported_on_non_binary(self, caster, expected_type):
+        expr = Column("x", JSON)
+
+        meth = getattr(expr, "as_%s" % caster)
+
+        assert_raises_message(
+            exc.InvalidRequestError,
+            r"The JSON cast operator JSON.as_%s\(\) only works" % caster,
+            meth,
+        )
+
+    @_caster_combinations
+    def test_cast_ops_unsupported_on_non_json_binary(
+        self, caster, expected_type
+    ):
+        expr = Column("x", JSON) + {"foo": "bar"}
+
+        meth = getattr(expr, "as_%s" % caster)
+
+        assert_raises_message(
+            exc.InvalidRequestError,
+            r"The JSON cast operator JSON.as_%s\(\) only works" % caster,
+            meth,
+        )
+
 
 class ArrayIndexOpTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     def setUp(self):