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

1 files changed, 48 insertions, 158 deletions

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