- Added :class:`.mysql.JSON` for MySQL 5.7. The JSON type provides

persistence of JSON values in MySQL as well as basic operator support
of "getitem" and "getpath", making use of the ``JSON_EXTRACT``
function in order to refer to individual paths in a JSON structure.
fixes #3547
- Added a new type to core :class:`.types.JSON`.  This is the
base of the PostgreSQL :class:`.postgresql.JSON` type as well as that
of the new :class:`.mysql.JSON` type, so that a PG/MySQL-agnostic
JSON column may be used.  The type features basic index and path
searching support.
fixes #3619
- reorganization of migration docs etc. to try to refer both to
the fixes to JSON that helps Postgresql while at the same time
indicating these are new features of the new base JSON type.
- a rework of the Array/Indexable system some more, moving things
that are specific to Array out of Indexable.
- new operators for JSON indexing added to core so that these can
be compiled by the PG and MySQL dialects individually
- rename sqltypes.Array to sqltypes.ARRAY - as there is no generic
Array implementation, this is an uppercase type for now, consistent
with the new sqltypes.JSON type that is also not a generic implementation.
There may need to be some convention change to handle the case of
datatypes that aren't generic, rely upon DB-native implementations,
but aren't necessarily all named the same thing.

2 files changed, 247 insertions, 2 deletions

diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 15bfad831..87c776e8c 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -487,6 +487,12 @@ class SuiteRequirements(Requirements):
         return exclusions.open()
 
     @property
+    def json_type(self):
+        """target platform implements a native JSON type."""
+
+        return exclusions.closed()
+
+    @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 230aeb1e9..c6e882fb5 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -5,7 +5,7 @@ from ..assertions import eq_
 from ..config import requirements
 from sqlalchemy import Integer, Unicode, UnicodeText, select
 from sqlalchemy import Date, DateTime, Time, MetaData, String, \
-    Text, Numeric, Float, literal, Boolean
+    Text, Numeric, Float, literal, Boolean, cast, null, JSON
 from ..schema import Table, Column
 from ... import testing
 import decimal
@@ -586,7 +586,246 @@ class BooleanTest(_LiteralRoundTripFixture, fixtures.TablesTest):
         )
 
 
-__all__ = ('UnicodeVarcharTest', 'UnicodeTextTest',
+class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
+    __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"}
+            }
+        }
+    }
+
+    @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 test_round_trip_data1(self):
+        self._test_round_trip(self.data1)
+
+    def _test_round_trip(self, data_element):
+        data_table = self.tables.data_table
+
+        config.db.execute(
+            data_table.insert(),
+            {'name': 'row1', 'data': data_element}
+        )
+
+        row = config.db.execute(
+            select([
+                data_table.c.data,
+            ])
+        ).first()
+
+        eq_(row, (data_element, ))
+
+    def test_round_trip_none_as_sql_null(self):
+        col = self.tables.data_table.c['nulldata']
+
+        with config.db.connect() as conn:
+            conn.execute(
+                self.tables.data_table.insert(),
+                {"name": "r1", "data": None}
+            )
+
+            eq_(
+                conn.scalar(
+                    select([self.tables.data_table.c.name]).
+                    where(col.is_(null()))
+                ),
+                "r1"
+            )
+
+            eq_(
+                conn.scalar(
+                    select([col])
+                ),
+                None
+            )
+
+    def test_round_trip_json_null_as_json_null(self):
+        col = self.tables.data_table.c['data']
+
+        with config.db.connect() as conn:
+            conn.execute(
+                self.tables.data_table.insert(),
+                {"name": "r1", "data": JSON.NULL}
+            )
+
+            eq_(
+                conn.scalar(
+                    select([self.tables.data_table.c.name]).
+                    where(cast(col, String) == 'null')
+                ),
+                "r1"
+            )
+
+            eq_(
+                conn.scalar(
+                    select([col])
+                ),
+                None
+            )
+
+    def test_round_trip_none_as_json_null(self):
+        col = self.tables.data_table.c['data']
+
+        with config.db.connect() as conn:
+            conn.execute(
+                self.tables.data_table.insert(),
+                {"name": "r1", "data": None}
+            )
+
+            eq_(
+                conn.scalar(
+                    select([self.tables.data_table.c.name]).
+                    where(cast(col, String) == 'null')
+                ),
+                "r1"
+            )
+
+            eq_(
+                conn.scalar(
+                    select([col])
+                ),
+                None
+            )
+
+    def _criteria_fixture(self):
+        config.db.execute(
+            self.tables.data_table.insert(),
+            [{"name": "r1", "data": self.data1},
+             {"name": "r2", "data": self.data2},
+             {"name": "r3", "data": self.data3},
+             {"name": "r4", "data": self.data4},
+             {"name": "r5", "data": self.data5}]
+        )
+
+    def _test_index_criteria(self, crit, expected):
+        self._criteria_fixture()
+        with config.db.connect() as conn:
+            eq_(
+                conn.scalar(
+                    select([self.tables.data_table.c.name]).
+                    where(crit)
+                ),
+                expected
+            )
+
+    def test_crit_spaces_in_key(self):
+        col = self.tables.data_table.c['data']
+        self._test_index_criteria(
+            cast(col["key two"], String) == '"value2"',
+            "r2"
+        )
+
+    def test_crit_simple_int(self):
+        col = self.tables.data_table.c['data']
+        self._test_index_criteria(
+            cast(col[1], String) == '"two"',
+            "r4"
+        )
+
+    def test_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):
+        col = self.tables.data_table.c['data']
+        self._test_index_criteria(
+            cast(col[("nested", "elem2", "elem3", "elem4")], String)
+            == '"elem5"',
+            "r5"
+        )
+
+    def test_unicode_round_trip(self):
+        s = select([
+            cast(
+                {
+                    util.u('réveillé'): util.u('réveillé'),
+                    "data": {"k1": util.u('drôle')}
+                },
+                self.datatype
+            )
+        ])
+        eq_(
+            config.db.scalar(s),
+            {
+                util.u('réveillé'): util.u('réveillé'),
+                "data": {"k1": util.u('drôle')}
+            },
+        )
+
+    def test_eval_none_flag_orm(self):
+        from sqlalchemy.ext.declarative import declarative_base
+        from sqlalchemy.orm import Session
+
+        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',
            'DateTest', 'DateTimeTest', 'TextTest',
            'NumericTest', 'IntegerTest',
            'DateTimeHistoricTest', 'DateTimeCoercedToDateTimeTest',