- 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.

6 files changed, 93 insertions, 185 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/array.py b/lib/sqlalchemy/dialects/postgresql/array.py
index b88f139de..f4316d318 100644
--- a/lib/sqlalchemy/dialects/postgresql/array.py
+++ b/lib/sqlalchemy/dialects/postgresql/array.py
@@ -84,12 +84,20 @@ class array(expression.Tuple):
         super(array, self).__init__(*clauses, **kw)
         self.type = ARRAY(self.type)
 
-    def _bind_param(self, operator, obj):
-        return array([
-            expression.BindParameter(None, o, _compared_to_operator=operator,
-                                     _compared_to_type=self.type, unique=True)
-            for o in obj
-        ])
+    def _bind_param(self, operator, obj, _assume_scalar=False, type_=None):
+        if _assume_scalar or operator is operators.getitem:
+            # if getitem->slice were called, Indexable produces
+            # a Slice object from that
+            assert isinstance(obj, int)
+            return expression.BindParameter(
+                None, obj, _compared_to_operator=operator,
+                type_=type_,
+                _compared_to_type=self.type, unique=True)
+
+        else:
+            return array([
+                self._bind_param(operator, o, _assume_scalar=True, type_=type_)
+                for o in obj])
 
     def self_group(self, against=None):
         if (against in (
@@ -106,15 +114,15 @@ CONTAINED_BY = operators.custom_op("<@", precedence=5)
 OVERLAP = operators.custom_op("&&", precedence=5)
 
 
-class ARRAY(SchemaEventTarget, sqltypes.Array):
+class ARRAY(SchemaEventTarget, sqltypes.ARRAY):
 
     """Postgresql ARRAY type.
 
     .. versionchanged:: 1.1 The :class:`.postgresql.ARRAY` type is now
-       a subclass of the core :class:`.Array` type.
+       a subclass of the core :class:`.types.ARRAY` type.
 
     The :class:`.postgresql.ARRAY` type is constructed in the same way
-    as the core :class:`.Array` type; a member type is required, and a
+    as the core :class:`.types.ARRAY` type; a member type is required, and a
     number of dimensions is recommended if the type is to be used for more
     than one dimension::
 
@@ -125,9 +133,9 @@ class ARRAY(SchemaEventTarget, sqltypes.Array):
             )
 
     The :class:`.postgresql.ARRAY` type provides all operations defined on the
-    core :class:`.Array` type, including support for "dimensions", indexed
-    access, and simple matching such as :meth:`.Array.Comparator.any`
-    and :meth:`.Array.Comparator.all`.  :class:`.postgresql.ARRAY` class also
+    core :class:`.types.ARRAY` type, including support for "dimensions", indexed
+    access, and simple matching such as :meth:`.types.ARRAY.Comparator.any`
+    and :meth:`.types.ARRAY.Comparator.all`.  :class:`.postgresql.ARRAY` class also
     provides PostgreSQL-specific methods for containment operations, including
     :meth:`.postgresql.ARRAY.Comparator.contains`
     :meth:`.postgresql.ARRAY.Comparator.contained_by`,
@@ -144,20 +152,20 @@ class ARRAY(SchemaEventTarget, sqltypes.Array):
 
     .. seealso::
 
-        :class:`.types.Array` - base array type
+        :class:`.types.ARRAY` - base array type
 
         :class:`.postgresql.array` - produces a literal array value.
 
     """
 
-    class Comparator(sqltypes.Array.Comparator):
+    class Comparator(sqltypes.ARRAY.Comparator):
 
         """Define comparison operations for :class:`.ARRAY`.
 
         Note that these operations are in addition to those provided
-        by the base :class:`.types.Array.Comparator` class, including
-        :meth:`.types.Array.Comparator.any` and
-        :meth:`.types.Array.Comparator.all`.
+        by the base :class:`.types.ARRAY.Comparator` class, including
+        :meth:`.types.ARRAY.Comparator.any` and
+        :meth:`.types.ARRAY.Comparator.all`.
 
         """
 
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index e9001f79a..3f9fcb27f 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1056,6 +1056,16 @@ class PGCompiler(compiler.SQLCompiler):
             self.process(element.stop, **kw),
         )
 
+    def visit_json_getitem_op_binary(self, binary, operator, **kw):
+        return self._generate_generic_binary(
+            binary, " -> ", **kw
+        )
+
+    def visit_json_path_getitem_op_binary(self, binary, operator, **kw):
+        return self._generate_generic_binary(
+            binary, " #> ", **kw
+        )
+
     def visit_getitem_binary(self, binary, operator, **kw):
         return "%s[%s]" % (
             self.process(binary.left, **kw),
diff --git a/lib/sqlalchemy/dialects/postgresql/ext.py b/lib/sqlalchemy/dialects/postgresql/ext.py
index 1a443c2d7..66c7ed0e5 100644
--- a/lib/sqlalchemy/dialects/postgresql/ext.py
+++ b/lib/sqlalchemy/dialects/postgresql/ext.py
@@ -159,7 +159,7 @@ static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
 def array_agg(*arg, **kw):
     """Postgresql-specific form of :class:`.array_agg`, ensures
     return type is :class:`.postgresql.ARRAY` and not
-    the plain :class:`.types.Array`.
+    the plain :class:`.types.ARRAY`.
 
     .. versionadded:: 1.1
 
diff --git a/lib/sqlalchemy/dialects/postgresql/hstore.py b/lib/sqlalchemy/dialects/postgresql/hstore.py
index b7b0fc007..d2d20386a 100644
--- a/lib/sqlalchemy/dialects/postgresql/hstore.py
+++ b/lib/sqlalchemy/dialects/postgresql/hstore.py
@@ -12,34 +12,33 @@ from .array import ARRAY
 from ... import types as sqltypes
 from ...sql import functions as sqlfunc
 from ...sql import operators
-from ...sql.operators import custom_op
 from ... import util
 
 __all__ = ('HSTORE', 'hstore')
 
 
-INDEX = custom_op(
-    "->", precedence=5, natural_self_precedent=True
+GETITEM = operators.custom_op(
+    "->", precedence=15, natural_self_precedent=True,
 )
 
 HAS_KEY = operators.custom_op(
-    "?", precedence=5, natural_self_precedent=True
+    "?", precedence=15, natural_self_precedent=True
 )
 
 HAS_ALL = operators.custom_op(
-    "?&", precedence=5, natural_self_precedent=True
+    "?&", precedence=15, natural_self_precedent=True
 )
 
 HAS_ANY = operators.custom_op(
-    "?|", precedence=5, natural_self_precedent=True
+    "?|", precedence=15, natural_self_precedent=True
 )
 
 CONTAINS = operators.custom_op(
-    "@>", precedence=5, natural_self_precedent=True
+    "@>", precedence=15, natural_self_precedent=True
 )
 
 CONTAINED_BY = operators.custom_op(
-    "<@", precedence=5, natural_self_precedent=True
+    "<@", precedence=15, natural_self_precedent=True
 )
 
 
@@ -166,7 +165,7 @@ class HSTORE(sqltypes.Indexable, sqltypes.Concatenable, sqltypes.TypeEngine):
                 CONTAINED_BY, other, result_type=sqltypes.Boolean)
 
         def _setup_getitem(self, index):
-            return INDEX, index, self.type.text_type
+            return GETITEM, index, self.type.text_type
 
         def defined(self, key):
             """Boolean expression.  Test for presence of a non-NULL value for
diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py
index 8a50270f5..6ff9fd88e 100644
--- a/lib/sqlalchemy/dialects/postgresql/json.py
+++ b/lib/sqlalchemy/dialects/postgresql/json.py
@@ -6,10 +6,10 @@
 # the MIT License: http://www.opensource.org/licenses/mit-license.php
 from __future__ import absolute_import
 
-import collections
 import json
+import collections
 
-from .base import ischema_names
+from .base import ischema_names, colspecs
 from ... import types as sqltypes
 from ...sql import operators
 from ...sql import elements
@@ -17,70 +17,68 @@ from ... import util
 
 __all__ = ('JSON', 'JSONB')
 
-
-# json : returns json
-INDEX = operators.custom_op(
-    "->", precedence=5, natural_self_precedent=True
-)
-
-# path operator: returns json
-PATHIDX = operators.custom_op(
-    "#>", precedence=5, natural_self_precedent=True
-)
-
-# json + astext: returns text
 ASTEXT = operators.custom_op(
-    "->>", precedence=5, natural_self_precedent=True
+    "->>", precedence=15, natural_self_precedent=True,
 )
 
-# path operator  + astext: returns text
-ASTEXT_PATHIDX = operators.custom_op(
-    "#>>", precedence=5, natural_self_precedent=True
+JSONPATH_ASTEXT = operators.custom_op(
+    "#>>", precedence=15, natural_self_precedent=True,
 )
 
+
 HAS_KEY = operators.custom_op(
-    "?", precedence=5, natural_self_precedent=True
+    "?", precedence=15, natural_self_precedent=True
 )
 
 HAS_ALL = operators.custom_op(
-    "?&", precedence=5, natural_self_precedent=True
+    "?&", precedence=15, natural_self_precedent=True
 )
 
 HAS_ANY = operators.custom_op(
-    "?|", precedence=5, natural_self_precedent=True
+    "?|", precedence=15, natural_self_precedent=True
 )
 
 CONTAINS = operators.custom_op(
-    "@>", precedence=5, natural_self_precedent=True
+    "@>", precedence=15, natural_self_precedent=True
 )
 
 CONTAINED_BY = operators.custom_op(
-    "<@", precedence=5, natural_self_precedent=True
+    "<@", precedence=15, natural_self_precedent=True
 )
 
 
-class JSON(sqltypes.Indexable, sqltypes.TypeEngine):
-    """Represent the Postgresql JSON type.
+class JSONPathType(sqltypes.JSON.JSONPathType):
+    def bind_processor(self, dialect):
+        def process(value):
+            assert isinstance(value, collections.Sequence)
+            tokens = [util.text_type(elem) for elem in value]
+            return "{%s}" % (", ".join(tokens))
 
-    The :class:`.JSON` type stores arbitrary JSON format data, e.g.::
+        return process
 
-        data_table = Table('data_table', metadata,
-            Column('id', Integer, primary_key=True),
-            Column('data', JSON)
-        )
+colspecs[sqltypes.JSON.JSONPathType] = JSONPathType
 
-        with engine.connect() as conn:
-            conn.execute(
-                data_table.insert(),
-                data = {"key1": "value1", "key2": "value2"}
-            )
 
-    :class:`.JSON` provides several operations:
+class JSON(sqltypes.JSON):
+    """Represent the Postgresql JSON type.
+
+    This type is a specialization of the Core-level :class:`.types.JSON`
+    type.   Be sure to read the documentation for :class:`.types.JSON` for
+    important tips regarding treatment of NULL values and ORM use.
+
+    .. versionchanged:: 1.1 :class:`.postgresql.JSON` is now a Postgresql-
+       specific specialization of the new :class:`.types.JSON` type.
+
+    The operators provided by the Postgresql version of :class:`.JSON`
+    include:
 
     * Index operations (the ``->`` operator)::
 
         data_table.c.data['some key']
 
+        data_table.c.data[5]
+
+
     * Index operations returning text (the ``->>`` operator)::
 
         data_table.c.data['some key'].astext == 'some value'
@@ -92,11 +90,11 @@ class JSON(sqltypes.Indexable, sqltypes.TypeEngine):
 
     * Path index operations (the ``#>`` operator)::
 
-        data_table.c.data[('key_1', 'key_2', ..., 'key_n')]
+        data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')]
 
     * Path index operations returning text (the ``#>>`` operator)::
 
-        data_table.c.data[('key_1', 'key_2', ..., 'key_n')].astext == \
+        data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')].astext == \
 'some value'
 
     .. versionchanged:: 1.1  The :meth:`.ColumnElement.cast` operator on
@@ -108,36 +106,6 @@ class JSON(sqltypes.Indexable, sqltypes.TypeEngine):
     :class:`.JSON` by default, so that further JSON-oriented instructions
     may be called upon the result type.
 
-    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
-    :mod:`sqlalchemy.ext.mutable` extension must be used.  This extension will
-    allow "in-place" changes to the datastructure to produce events which
-    will be detected by the unit of work.  See the example at :class:`.HSTORE`
-    for a simple example involving a dictionary.
-
-    When working with NULL values, the :class:`.JSON` type recommends the
-    use of two specific constants in order to differentiate between a column
-    that evaluates to SQL NULL, e.g. no value, vs. the JSON-encoded string
-    of ``"null"``.   To insert or select against a value that is SQL NULL,
-    use the constant :func:`.null`::
-
-        conn.execute(table.insert(), json_value=null())
-
-    To insert or select against a value that is JSON ``"null"``, use the
-    constant :attr:`.JSON.NULL`::
-
-        conn.execute(table.insert(), json_value=JSON.NULL)
-
-    The :class:`.JSON` type supports a flag
-    :paramref:`.JSON.none_as_null` which when set to True will result
-    in the Python constant ``None`` evaluating to the value of SQL
-    NULL, and when set to False results in the Python constant
-    ``None`` evaluating to the value of JSON ``"null"``.    The Python
-    value ``None`` may be used in conjunction with either
-    :attr:`.JSON.NULL` and :func:`.null` in order to indicate NULL
-    values, but care must be taken as to the value of the
-    :paramref:`.JSON.none_as_null` in these cases.
-
     Custom serializers and deserializers are specified at the dialect level,
     that is using :func:`.create_engine`.  The reason for this is that when
     using psycopg2, the DBAPI only allows serializers at the per-cursor
@@ -151,43 +119,16 @@ class JSON(sqltypes.Indexable, sqltypes.TypeEngine):
     When using the psycopg2 dialect, the json_deserializer is registered
     against the database using ``psycopg2.extras.register_default_json``.
 
-    .. versionadded:: 0.9
-
     .. seealso::
 
+        :class:`.types.JSON` - Core level JSON type
+
         :class:`.JSONB`
 
     """
 
-    __visit_name__ = 'JSON'
-
-    hashable = False
     astext_type = sqltypes.Text()
 
-    NULL = util.symbol('JSON_NULL')
-    """Describe the json value of NULL.
-
-    This value is used to force the JSON value of ``"null"`` to be
-    used as the value.   A value of Python ``None`` will be recognized
-    either as SQL NULL or JSON ``"null"``, based on the setting
-    of the :paramref:`.JSON.none_as_null` flag; the :attr:`.JSON.NULL`
-    constant can be used to always resolve to JSON ``"null"`` regardless
-    of this setting.  This is in contrast to the :func:`.sql.null` construct,
-    which always resolves to SQL NULL.  E.g.::
-
-        from sqlalchemy import null
-        from sqlalchemy.dialects.postgresql import JSON
-
-        obj1 = MyObject(json_value=null())  # will *always* insert SQL NULL
-        obj2 = MyObject(json_value=JSON.NULL)  # will *always* insert JSON string "null"
-
-        session.add_all([obj1, obj2])
-        session.commit()
-
-    .. versionadded:: 1.1
-
-    """
-
     def __init__(self, none_as_null=False, astext_type=None):
         """Construct a :class:`.JSON` type.
 
@@ -210,15 +151,14 @@ class JSON(sqltypes.Indexable, sqltypes.TypeEngine):
          :attr:`.JSON.Comparator.astext`
          accessor on indexed attributes.  Defaults to :class:`.types.Text`.
 
-         .. versionadded:: 1.1.0
+         .. versionadded:: 1.1
 
          """
-        self.none_as_null = none_as_null
+        super(JSON, self).__init__(none_as_null=none_as_null)
         if astext_type is not None:
             self.astext_type = astext_type
 
-    class Comparator(
-            sqltypes.Indexable.Comparator, sqltypes.Concatenable.Comparator):
+    class Comparator(sqltypes.JSON.Comparator):
         """Define comparison operations for :class:`.JSON`."""
 
         @property
@@ -235,69 +175,19 @@ class JSON(sqltypes.Indexable, sqltypes.TypeEngine):
                 :meth:`.ColumnElement.cast`
 
             """
-            against = self.expr.operator
-            if against is PATHIDX:
-                against = ASTEXT_PATHIDX
-            else:
-                against = ASTEXT
 
-            return self.expr.left.operate(
-                against, self.expr.right, result_type=self.type.astext_type)
-
-        def _setup_getitem(self, index):
-            if not isinstance(index, util.string_types):
-                assert isinstance(index, collections.Sequence)
-                tokens = [util.text_type(elem) for elem in index]
-                index = "{%s}" % (", ".join(tokens))
-                operator = PATHIDX
+            if isinstance(self.expr.right.type, sqltypes.JSON.JSONPathType):
+                return self.expr.left.operate(
+                    JSONPATH_ASTEXT,
+                    self.expr.right, result_type=self.type.astext_type)
             else:
-                operator = INDEX
-
-            return operator, index, self.type
+                return self.expr.left.operate(
+                    ASTEXT, self.expr.right, result_type=self.type.astext_type)
 
     comparator_factory = Comparator
 
-    @property
-    def should_evaluate_none(self):
-        return not self.none_as_null
-
-    def bind_processor(self, dialect):
-        json_serializer = dialect._json_serializer or json.dumps
-        if util.py2k:
-            encoding = dialect.encoding
-        else:
-            encoding = None
-
-        def process(value):
-            if value is self.NULL:
-                value = None
-            elif isinstance(value, elements.Null) or (
-                value is None and self.none_as_null
-            ):
-                return None
-            if encoding:
-                return json_serializer(value).encode(encoding)
-            else:
-                return json_serializer(value)
-
-        return process
-
-    def result_processor(self, dialect, coltype):
-        json_deserializer = dialect._json_deserializer or json.loads
-        if util.py2k:
-            encoding = dialect.encoding
-        else:
-            encoding = None
-
-        def process(value):
-            if value is None:
-                return None
-            if encoding:
-                value = value.decode(encoding)
-            return json_deserializer(value)
-        return process
-
 
+colspecs[sqltypes.JSON] = JSON
 ischema_names['json'] = JSON
 
 
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index d33554922..82fcc9054 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -534,6 +534,7 @@ class PGDialect_psycopg2(PGDialect):
             sqltypes.Enum: _PGEnum,  # needs force_unicode
             HSTORE: _PGHStore,
             JSON: _PGJSON,
+            sqltypes.JSON: _PGJSON,
             JSONB: _PGJSONB,
             UUID: _PGUUID
         }