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

2 files changed, 209 insertions, 87 deletions

diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index fd8d82690..5b26ac72e 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -686,6 +686,10 @@ class SuiteRequirements(Requirements):
         return self.json_type
 
     @property
+    def json_index_supplementary_unicode_element(self):
+        return exclusions.open()
+
+    @property
     def precision_numerics_general(self):
         """target backend has general support for moderately high-precision
         numerics."""
diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py
index 435ab4689..bf5b18d0e 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -9,6 +9,7 @@ from .. import engines
 from .. import fixtures
 from .. import mock
 from ..assertions import eq_
+from ..assertions import is_
 from ..config import requirements
 from ..schema import Column
 from ..schema import Table
@@ -710,31 +711,6 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
 
     datatype = JSON
 
-    data1 = {"key1": "value1", "key2": "value2"}
-
-    data2 = {
-        "Key 'One'": "value1",
-        "key two": "value2",
-        "key three": "value ' three '",
-    }
-
-    data3 = {
-        "key1": [1, 2, 3],
-        "key2": ["one", "two", "three"],
-        "key3": [{"four": "five"}, {"six": "seven"}],
-    }
-
-    data4 = ["one", "two", "three"]
-
-    data5 = {
-        "nested": {
-            "elem1": [{"a": "b", "c": "d"}, {"e": "f", "g": "h"}],
-            "elem2": {"elem3": {"elem4": "elem5"}},
-        }
-    }
-
-    data6 = {"a": 5, "b": "some value", "c": {"foo": "bar"}}
-
     @classmethod
     def define_tables(cls, metadata):
         Table(
@@ -747,7 +723,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
         )
 
     def test_round_trip_data1(self):
-        self._test_round_trip(self.data1)
+        self._test_round_trip({"key1": "value1", "key2": "value2"})
 
     def _test_round_trip(self, data_element):
         data_table = self.tables.data_table
@@ -760,6 +736,97 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
 
         eq_(row, (data_element,))
 
+    def _index_fixtures(fn):
+        fn = testing.combinations(
+            ("boolean", True),
+            ("boolean", False),
+            ("boolean", None),
+            ("string", "some string"),
+            ("string", None),
+            ("string", util.u("réve illé")),
+            (
+                "string",
+                util.u("réve🐍 illé"),
+                testing.requires.json_index_supplementary_unicode_element,
+            ),
+            ("integer", 15),
+            ("integer", 1),
+            ("integer", 0),
+            ("integer", None),
+            ("float", 28.5),
+            ("float", None),
+            # TODO: how to test for comaprison
+            #        ("json", {"foo": "bar"}),
+            id_="sa",
+        )(fn)
+        return fn
+
+    @_index_fixtures
+    def test_index_typed_access(self, datatype, value):
+        data_table = self.tables.data_table
+        data_element = {"key1": value}
+        with config.db.connect() as conn:
+            conn.execute(
+                data_table.insert(),
+                {
+                    "name": "row1",
+                    "data": data_element,
+                    "nulldata": data_element,
+                },
+            )
+
+            expr = data_table.c.data["key1"]
+            expr = getattr(expr, "as_%s" % datatype)()
+
+            roundtrip = conn.scalar(select([expr]))
+            eq_(roundtrip, value)
+            if util.py3k:  # skip py2k to avoid comparing unicode to str etc.
+                is_(type(roundtrip), type(value))
+
+    @_index_fixtures
+    def test_index_typed_comparison(self, datatype, value):
+        data_table = self.tables.data_table
+        data_element = {"key1": value}
+        with config.db.connect() as conn:
+            conn.execute(
+                data_table.insert(),
+                {
+                    "name": "row1",
+                    "data": data_element,
+                    "nulldata": data_element,
+                },
+            )
+
+            expr = data_table.c.data["key1"]
+            expr = getattr(expr, "as_%s" % datatype)()
+
+            row = conn.execute(select([expr]).where(expr == value)).first()
+
+            # make sure we get a row even if value is None
+            eq_(row, (value,))
+
+    @_index_fixtures
+    def test_path_typed_comparison(self, datatype, value):
+        data_table = self.tables.data_table
+        data_element = {"key1": {"subkey1": value}}
+        with config.db.connect() as conn:
+            conn.execute(
+                data_table.insert(),
+                {
+                    "name": "row1",
+                    "data": data_element,
+                    "nulldata": data_element,
+                },
+            )
+
+            expr = data_table.c.data[("key1", "subkey1")]
+            expr = getattr(expr, "as_%s" % datatype)()
+
+            row = conn.execute(select([expr]).where(expr == value)).first()
+
+            # make sure we get a row even if value is None
+            eq_(row, (value,))
+
     def test_round_trip_custom_json(self):
         data_table = self.tables.data_table
         data_element = {"key1": "data1"}
@@ -840,6 +907,112 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
 
             eq_(conn.scalar(select([col])), None)
 
+    def test_unicode_round_trip(self):
+        # note we include Unicode supplementary characters as well
+        with config.db.connect() as conn:
+            conn.execute(
+                self.tables.data_table.insert(),
+                {
+                    "name": "r1",
+                    "data": {
+                        util.u("réve🐍 illé"): util.u("réve🐍 illé"),
+                        "data": {"k1": util.u("drôl🐍e")},
+                    },
+                },
+            )
+
+            eq_(
+                conn.scalar(select([self.tables.data_table.c.data])),
+                {
+                    util.u("réve🐍 illé"): util.u("réve🐍 illé"),
+                    "data": {"k1": util.u("drôl🐍e")},
+                },
+            )
+
+    def test_eval_none_flag_orm(self):
+
+        Base = declarative_base()
+
+        class Data(Base):
+            __table__ = self.tables.data_table
+
+        s = Session(testing.db)
+
+        d1 = Data(name="d1", data=None, nulldata=None)
+        s.add(d1)
+        s.commit()
+
+        s.bulk_insert_mappings(
+            Data, [{"name": "d2", "data": None, "nulldata": None}]
+        )
+        eq_(
+            s.query(
+                cast(self.tables.data_table.c.data, String()),
+                cast(self.tables.data_table.c.nulldata, String),
+            )
+            .filter(self.tables.data_table.c.name == "d1")
+            .first(),
+            ("null", None),
+        )
+        eq_(
+            s.query(
+                cast(self.tables.data_table.c.data, String()),
+                cast(self.tables.data_table.c.nulldata, String),
+            )
+            .filter(self.tables.data_table.c.name == "d2")
+            .first(),
+            ("null", None),
+        )
+
+
+class JSONStringCastIndexTest(_LiteralRoundTripFixture, fixtures.TablesTest):
+    """test JSON index access with "cast to string", which we have documented
+    for a long time as how to compare JSON values, but is ultimately not
+    reliable in all cases.
+
+    """
+
+    __requires__ = ("json_type",)
+    __backend__ = True
+
+    datatype = JSON
+
+    data1 = {"key1": "value1", "key2": "value2"}
+
+    data2 = {
+        "Key 'One'": "value1",
+        "key two": "value2",
+        "key three": "value ' three '",
+    }
+
+    data3 = {
+        "key1": [1, 2, 3],
+        "key2": ["one", "two", "three"],
+        "key3": [{"four": "five"}, {"six": "seven"}],
+    }
+
+    data4 = ["one", "two", "three"]
+
+    data5 = {
+        "nested": {
+            "elem1": [{"a": "b", "c": "d"}, {"e": "f", "g": "h"}],
+            "elem2": {"elem3": {"elem4": "elem5"}},
+        }
+    }
+
+    data6 = {"a": 5, "b": "some value", "c": {"foo": "bar"}}
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table(
+            "data_table",
+            metadata,
+            Column("id", Integer, primary_key=True),
+            Column("name", String(30), nullable=False),
+            Column("data", cls.datatype),
+            Column("nulldata", cls.datatype(none_as_null=True)),
+        )
+
     def _criteria_fixture(self):
         config.db.execute(
             self.tables.data_table.insert(),
@@ -869,7 +1042,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
 
                 eq_(conn.scalar(literal_sql), expected)
 
-    def test_crit_spaces_in_key(self):
+    def test_string_cast_crit_spaces_in_key(self):
         name = self.tables.data_table.c.name
         col = self.tables.data_table.c["data"]
 
@@ -885,7 +1058,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
         )
 
     @config.requirements.json_array_indexes
-    def test_crit_simple_int(self):
+    def test_string_cast_crit_simple_int(self):
         name = self.tables.data_table.c.name
         col = self.tables.data_table.c["data"]
 
@@ -896,13 +1069,13 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
             and_(name == "r4", cast(col[1], String) == '"two"'), "r4"
         )
 
-    def test_crit_mixed_path(self):
+    def test_string_cast_crit_mixed_path(self):
         col = self.tables.data_table.c["data"]
         self._test_index_criteria(
             cast(col[("key3", 1, "six")], String) == '"seven"', "r3"
         )
 
-    def test_crit_string_path(self):
+    def test_string_cast_crit_string_path(self):
         col = self.tables.data_table.c["data"]
         self._test_index_criteria(
             cast(col[("nested", "elem2", "elem3", "elem4")], String)
@@ -910,7 +1083,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
             "r5",
         )
 
-    def test_crit_against_string_basic(self):
+    def test_string_cast_crit_against_string_basic(self):
         name = self.tables.data_table.c.name
         col = self.tables.data_table.c["data"]
 
@@ -949,67 +1122,12 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
             test_literal=False,
         )
 
-    def test_unicode_round_trip(self):
-        with config.db.connect() as conn:
-            conn.execute(
-                self.tables.data_table.insert(),
-                {
-                    "name": "r1",
-                    "data": {
-                        util.u("réve🐍 illé"): util.u("réve🐍 illé"),
-                        "data": {"k1": util.u("drôl🐍e")},
-                    },
-                },
-            )
-
-            eq_(
-                conn.scalar(select([self.tables.data_table.c.data])),
-                {
-                    util.u("réve🐍 illé"): util.u("réve🐍 illé"),
-                    "data": {"k1": util.u("drôl🐍e")},
-                },
-            )
-
-    def test_eval_none_flag_orm(self):
-
-        Base = declarative_base()
-
-        class Data(Base):
-            __table__ = self.tables.data_table
-
-        s = Session(testing.db)
-
-        d1 = Data(name="d1", data=None, nulldata=None)
-        s.add(d1)
-        s.commit()
-
-        s.bulk_insert_mappings(
-            Data, [{"name": "d2", "data": None, "nulldata": None}]
-        )
-        eq_(
-            s.query(
-                cast(self.tables.data_table.c.data, String()),
-                cast(self.tables.data_table.c.nulldata, String),
-            )
-            .filter(self.tables.data_table.c.name == "d1")
-            .first(),
-            ("null", None),
-        )
-        eq_(
-            s.query(
-                cast(self.tables.data_table.c.data, String()),
-                cast(self.tables.data_table.c.nulldata, String),
-            )
-            .filter(self.tables.data_table.c.name == "d2")
-            .first(),
-            ("null", None),
-        )
-
 
 __all__ = (
     "UnicodeVarcharTest",
     "UnicodeTextTest",
     "JSONTest",
+    "JSONStringCastIndexTest",
     "DateTest",
     "DateTimeTest",
     "TextTest",