- rework the JSON expression system so that "astext" is called *after*

the indexing.  this is for more natural operation.
- also add cast() to the JSON expression to complement astext. This integrates
the CAST call which will be needed frequently.  Part of [ticket:2687].
- it's a little unclear how more advanced unicode attribute-access is going to go,
some quick attempts at testing yielded strange error messages from psycopg2.
- do other cross linking as mentioned in [ticket:2687].

6 files changed, 161 insertions, 55 deletions

diff --git a/doc/build/changelog/migration_09.rst b/doc/build/changelog/migration_09.rst
index a8be06fda..6d374cf7a 100644
--- a/doc/build/changelog/migration_09.rst
+++ b/doc/build/changelog/migration_09.rst
@@ -920,6 +920,19 @@ from a backref::
 
 :ticket:`1535`
 
+
+Postgresql JSON Type
+--------------------
+
+The Postgresql dialect now features a :class:`.postgresql.JSON` type to
+complement the :class:`.postgresql.HSTORE` type.
+
+.. seealso::
+
+    :class:`.postgresql.JSON`
+
+:ticket:`2581`
+
 Behavioral Improvements
 =======================
 
diff --git a/doc/build/dialects/postgresql.rst b/doc/build/dialects/postgresql.rst
index 965ba7ebc..05b63506e 100644
--- a/doc/build/dialects/postgresql.rst
+++ b/doc/build/dialects/postgresql.rst
@@ -72,6 +72,9 @@ construction arguments, are as follows:
 .. autoclass:: JSON
     :members:
 
+.. autoclass:: JSONElement
+    :members:
+
 .. autoclass:: MACADDR
     :members: __init__
 
diff --git a/lib/sqlalchemy/dialects/postgresql/__init__.py b/lib/sqlalchemy/dialects/postgresql/__init__.py
index cfe1ebce0..7f493e522 100644
--- a/lib/sqlalchemy/dialects/postgresql/__init__.py
+++ b/lib/sqlalchemy/dialects/postgresql/__init__.py
@@ -15,7 +15,7 @@ from .base import \
     TSVECTOR
 from .constraints import ExcludeConstraint
 from .hstore import HSTORE, hstore
-from .json import JSON
+from .json import JSON, JSONElement
 from .ranges import INT4RANGE, INT8RANGE, NUMRANGE, DATERANGE, TSRANGE, \
     TSTZRANGE
 
@@ -25,5 +25,5 @@ __all__ = (
     'DOUBLE_PRECISION', 'TIMESTAMP', 'TIME', 'DATE', 'BYTEA', 'BOOLEAN',
     'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'Any', 'All', 'array', 'HSTORE',
     'hstore', 'INT4RANGE', 'INT8RANGE', 'NUMRANGE', 'DATERANGE',
-    'TSRANGE', 'TSTZRANGE', 'json', 'JSON'
+    'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONElement'
 )
diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py
index 7ba8b1abe..7f8aad51e 100644
--- a/lib/sqlalchemy/dialects/postgresql/json.py
+++ b/lib/sqlalchemy/dialects/postgresql/json.py
@@ -10,9 +10,83 @@ import json
 from .base import ischema_names
 from ... import types as sqltypes
 from ...sql.operators import custom_op
+from ... import sql
+from ...sql import elements
 from ... import util
 
-__all__ = ('JSON', )
+__all__ = ('JSON', 'JSONElement')
+
+
+class JSONElement(elements.BinaryExpression):
+    """Represents accessing an element of a :class:`.JSON` value.
+
+    The :class:`.JSONElement` is produced whenever using the Python index
+    operator on an expression that has the type :class:`.JSON`::
+
+        expr = mytable.c.json_data['some_key']
+
+    The expression typically compiles to a JSON access such as ``col -> key``.
+    Modifiers are then available for typing behavior, including :meth:`.JSONElement.cast`
+    and :attr:`.JSONElement.astext`.
+
+    """
+    def __init__(self, left, right, astext=False, opstring=None, result_type=None):
+        self._astext = astext
+        if opstring is None:
+            if hasattr(right, '__iter__') and \
+                not isinstance(right, util.string_types):
+                opstring = "#>"
+                right = "{%s}" % (", ".join(util.text_type(elem) for elem in right))
+            else:
+                opstring = "->"
+
+        self._json_opstring = opstring
+        operator = custom_op(opstring, precedence=5)
+        right = left._check_literal(left, operator, right)
+        super(JSONElement, self).__init__(left, right, operator, type_=result_type)
+
+    @property
+    def astext(self):
+        """Convert this :class:`.JSONElement` to use the 'astext' operator
+        when evaluated.
+
+        E.g.::
+
+            select([data_table.c.data['some key'].astext])
+
+        .. seealso::
+
+            :meth:`.JSONElement.cast`
+
+        """
+        if self._astext:
+            return self
+        else:
+            return JSONElement(
+                    self.left,
+                    self.right,
+                    astext=True,
+                    opstring=self._json_opstring + ">",
+                    result_type=sqltypes.String(convert_unicode=True)
+                )
+
+    def cast(self, type_):
+        """Convert this :class:`.JSONElement` to apply both the 'astext' operator
+        as well as an explicit type cast when evaulated.
+
+        E.g.::
+
+            select([data_table.c.data['some key'].cast(Integer)])
+
+        .. seealso::
+
+            :attr:`.JSONElement.astext`
+
+        """
+        if not self._astext:
+            return self.astext.cast(type_)
+        else:
+            return sql.cast(self, type_)
 
 
 class JSON(sqltypes.TypeEngine):
@@ -37,17 +111,26 @@ class JSON(sqltypes.TypeEngine):
 
         data_table.c.data['some key']
 
-    * Index operations returning text (required for text comparison or casting)::
+    * Index operations returning text (required for text comparison)::
+
+        data_table.c.data['some key'].astext == 'some value'
+
+    * Index operations with a built-in CAST call::
 
-        data_table.c.data.astext['some key'] == 'some value'
+        data_table.c.data['some key'].cast(Integer) == 5
 
     * Path index operations::
 
         data_table.c.data[('key_1', 'key_2', ..., 'key_n')]
 
-    * Path index operations returning text (required for text comparison or casting)::
+    * Path index operations returning text (required for text comparison)::
 
-        data_table.c.data.astext[('key_1', 'key_2', ..., 'key_n')] == 'some value'
+        data_table.c.data[('key_1', 'key_2', ..., 'key_n')].astext == 'some value'
+
+    Index operations return an instance of :class:`.JSONElement`, which represents
+    an expression such as ``column -> index``.  This element then defines
+    methods such as :attr:`.JSONElement.astext` and :meth:`.JSONElement.cast`
+    for setting up type behavior.
 
     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
@@ -78,35 +161,10 @@ class JSON(sqltypes.TypeEngine):
     class comparator_factory(sqltypes.Concatenable.Comparator):
         """Define comparison operations for :class:`.JSON`."""
 
-        class _astext(object):
-            def __init__(self, parent):
-                self.parent = parent
-
-            def __getitem__(self, other):
-                return self.parent.expr._get_item(other, True)
-
-        def _get_item(self, other, astext):
-            if hasattr(other, '__iter__') and \
-                not isinstance(other, util.string_types):
-                op = "#>"
-                other = "{%s}" % (", ".join(util.text_type(elem) for elem in other))
-            else:
-                op = "->"
-
-            if astext:
-                op += ">"
-
-            # ops: ->, ->>, #>, #>>
-            return self.expr.op(op, precedence=5)(other)
-
         def __getitem__(self, other):
             """Get the value at a given key."""
 
-            return self._get_item(other, False)
-
-        @property
-        def astext(self):
-            return self._astext(self)
+            return JSONElement(self.expr, other)
 
         def _adapt_expression(self, op, other_comparator):
             if isinstance(op, custom_op):
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 91ce0a090..dfebf09a8 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -80,7 +80,7 @@ def literal(value, type_=None):
 
 
 
-def type_coerce(expr, type_):
+def type_coerce(expression, type_):
     """Coerce the given expression into the given type,
     on the Python side only.
 
@@ -116,22 +116,30 @@ def type_coerce(expr, type_):
                     )
         )
 
+    :param expression: Column-oriented expression.
+    :param type_: A :class:`.TypeEngine` class or instance indicating
+     the type to which the CAST should apply.
+
+    .. seealso::
+
+        :func:`.cast`
+
     """
     type_ = type_api.to_instance(type_)
 
-    if hasattr(expr, '__clause_element__'):
-        return type_coerce(expr.__clause_element__(), type_)
-    elif isinstance(expr, BindParameter):
-        bp = expr._clone()
+    if hasattr(expression, '__clause_element__'):
+        return type_coerce(expression.__clause_element__(), type_)
+    elif isinstance(expression, BindParameter):
+        bp = expression._clone()
         bp.type = type_
         return bp
-    elif not isinstance(expr, Visitable):
-        if expr is None:
+    elif not isinstance(expression, Visitable):
+        if expression is None:
             return Null()
         else:
-            return literal(expr, type_=type_)
+            return literal(expression, type_=type_)
     else:
-        return Label(None, expr, type_=type_)
+        return Label(None, expression, type_=type_)
 
 
 
@@ -1734,12 +1742,12 @@ class Cast(ColumnElement):
 
     __visit_name__ = 'cast'
 
-    def __init__(self, clause, totype, **kwargs):
+    def __init__(self, expression, type_):
         """Return a :class:`.Cast` object.
 
         Equivalent of SQL ``CAST(clause AS totype)``.
 
-        Use with a :class:`~sqlalchemy.types.TypeEngine` subclass, i.e::
+        E.g.::
 
           cast(table.c.unit_price * table.c.qty, Numeric(10,4))
 
@@ -1747,12 +1755,18 @@ class Cast(ColumnElement):
 
           cast(table.c.timestamp, DATE)
 
-        :class:`.Cast` is available using :func:`.cast` or alternatively
-        ``func.cast`` from the :data:`.func` namespace.
+        :param expression: Column-oriented expression.
+        :param type_: A :class:`.TypeEngine` class or instance indicating
+         the type to which the CAST should apply.
+
+        .. seealso::
+
+            :func:`.type_coerce` - Python-side type coercion without emitting
+            CAST.
 
         """
-        self.type = type_api.to_instance(totype)
-        self.clause = _literal_as_binds(clause, None)
+        self.type = type_api.to_instance(type_)
+        self.clause = _literal_as_binds(expression, None)
         if isinstance(self.clause, BindParameter) and (
                 self.clause.type._isnull
                 or self.clause.type._type_affinity is self.type._type_affinity
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index bcb3e1ebb..ba4b63e1a 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -1715,13 +1715,20 @@ class JSONTest(AssertsCompiledSQL, fixtures.TestBase):
 
     def test_where_getitem_as_text(self):
         self._test_where(
-            self.jsoncol.astext['bar'] == None,
+            self.jsoncol['bar'].astext == None,
             "(test_table.test_column ->> %(test_column_1)s) IS NULL"
         )
 
+    def test_where_getitem_as_cast(self):
+        self._test_where(
+            self.jsoncol['bar'].cast(Integer) == 5,
+            "CAST(test_table.test_column ->> %(test_column_1)s AS INTEGER) "
+            "= %(param_1)s"
+        )
+
     def test_where_path_as_text(self):
         self._test_where(
-            self.jsoncol.astext[("foo", 1)] == None,
+            self.jsoncol[("foo", 1)].astext == None,
             "(test_table.test_column #>> %(test_column_1)s) IS NULL"
         )
 
@@ -1752,7 +1759,7 @@ class JSONRoundTripTest(fixtures.TablesTest):
                 {'name': 'r2', 'data': {"k1": "r2v1", "k2": "r2v2"}},
                 {'name': 'r3', 'data': {"k1": "r3v1", "k2": "r3v2"}},
                 {'name': 'r4', 'data': {"k1": "r4v1", "k2": "r4v2"}},
-                {'name': 'r5', 'data': {"k1": "r5v1", "k2": "r5v2"}},
+                {'name': 'r5', 'data': {"k1": "r5v1", "k2": "r5v2", "k3": 5}},
         )
 
     def _assert_data(self, compare):
@@ -1875,7 +1882,7 @@ class JSONRoundTripTest(fixtures.TablesTest):
         data_table = self.tables.data_table
         result = engine.execute(
             select([data_table.c.data]).where(
-                data_table.c.data.astext[('k1',)] == 'r3v1'
+                data_table.c.data[('k1',)].astext == 'r3v1'
             )
         ).first()
         eq_(result, ({'k1': 'r3v1', 'k2': 'r3v2'},))
@@ -1885,15 +1892,25 @@ class JSONRoundTripTest(fixtures.TablesTest):
         self._fixture_data(engine)
         data_table = self.tables.data_table
         result = engine.execute(
-            select([data_table.c.data.astext['k1']])
+            select([data_table.c.data['k1'].astext])
         ).first()
-        assert isinstance(result[0], basestring)
+        assert isinstance(result[0], util.text_type)
+
+    def test_query_returned_as_int(self):
+        engine = testing.db
+        self._fixture_data(engine)
+        data_table = self.tables.data_table
+        result = engine.execute(
+            select([data_table.c.data['k3'].cast(Integer)]).where(
+                    data_table.c.name == 'r5')
+        ).first()
+        assert isinstance(result[0], int)
 
     def _test_criterion(self, engine):
         data_table = self.tables.data_table
         result = engine.execute(
             select([data_table.c.data]).where(
-                data_table.c.data.astext['k1'] == 'r3v1'
+                data_table.c.data['k1'].astext == 'r3v1'
             )
         ).first()
         eq_(result, ({'k1': 'r3v1', 'k2': 'r3v2'},))
@@ -1943,6 +1960,7 @@ class JSONRoundTripTest(fixtures.TablesTest):
                 },
         )
 
+
     def test_unicode_round_trip_python(self):
         engine = self._non_native_engine()
         self._test_unicode_round_trip(engine)