1# postgresql/json.py
2# Copyright (C) 2005-2018 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: http://www.opensource.org/licenses/mit-license.php
7from __future__ import absolute_import
8
9import json
10import collections
11
12from .base import ischema_names, colspecs
13from ... import types as sqltypes
14from ...sql import operators
15from ...sql import elements
16from ... import util
17
18__all__ = ('JSON', 'JSONB')
19
20idx_precedence = operators._PRECEDENCE[operators.json_getitem_op]
21
22ASTEXT = operators.custom_op(
23    "->>", precedence=idx_precedence, natural_self_precedent=True,
24    eager_grouping=True
25)
26
27JSONPATH_ASTEXT = operators.custom_op(
28    "#>>", precedence=idx_precedence, natural_self_precedent=True,
29    eager_grouping=True
30)
31
32
33HAS_KEY = operators.custom_op(
34    "?", precedence=idx_precedence, natural_self_precedent=True,
35    eager_grouping=True
36)
37
38HAS_ALL = operators.custom_op(
39    "?&", precedence=idx_precedence, natural_self_precedent=True,
40    eager_grouping=True
41)
42
43HAS_ANY = operators.custom_op(
44    "?|", precedence=idx_precedence, natural_self_precedent=True,
45    eager_grouping=True
46)
47
48CONTAINS = operators.custom_op(
49    "@>", precedence=idx_precedence, natural_self_precedent=True,
50    eager_grouping=True
51)
52
53CONTAINED_BY = operators.custom_op(
54    "<@", precedence=idx_precedence, natural_self_precedent=True,
55    eager_grouping=True
56)
57
58
59class JSONPathType(sqltypes.JSON.JSONPathType):
60    def bind_processor(self, dialect):
61        super_proc = self.string_bind_processor(dialect)
62
63        def process(value):
64            assert isinstance(value, collections.Sequence)
65            tokens = [util.text_type(elem)for elem in value]
66            value = "{%s}" % (", ".join(tokens))
67            if super_proc:
68                value = super_proc(value)
69            return value
70
71        return process
72
73    def literal_processor(self, dialect):
74        super_proc = self.string_literal_processor(dialect)
75
76        def process(value):
77            assert isinstance(value, collections.Sequence)
78            tokens = [util.text_type(elem)for elem in value]
79            value = "{%s}" % (", ".join(tokens))
80            if super_proc:
81                value = super_proc(value)
82            return value
83
84        return process
85
86colspecs[sqltypes.JSON.JSONPathType] = JSONPathType
87
88
89class JSON(sqltypes.JSON):
90    """Represent the PostgreSQL JSON type.
91
92    This type is a specialization of the Core-level :class:`.types.JSON`
93    type.   Be sure to read the documentation for :class:`.types.JSON` for
94    important tips regarding treatment of NULL values and ORM use.
95
96    .. versionchanged:: 1.1 :class:`.postgresql.JSON` is now a PostgreSQL-
97       specific specialization of the new :class:`.types.JSON` type.
98
99    The operators provided by the PostgreSQL version of :class:`.JSON`
100    include:
101
102    * Index operations (the ``->`` operator)::
103
104        data_table.c.data['some key']
105
106        data_table.c.data[5]
107
108
109    * Index operations returning text (the ``->>`` operator)::
110
111        data_table.c.data['some key'].astext == 'some value'
112
113    * Index operations with CAST
114      (equivalent to ``CAST(col ->> ['some key'] AS <type>)``)::
115
116        data_table.c.data['some key'].astext.cast(Integer) == 5
117
118    * Path index operations (the ``#>`` operator)::
119
120        data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')]
121
122    * Path index operations returning text (the ``#>>`` operator)::
123
124        data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')].astext == \
125'some value'
126
127    .. versionchanged:: 1.1  The :meth:`.ColumnElement.cast` operator on
128       JSON objects now requires that the :attr:`.JSON.Comparator.astext`
129       modifier be called explicitly, if the cast works only from a textual
130       string.
131
132    Index operations return an expression object whose type defaults to
133    :class:`.JSON` by default, so that further JSON-oriented instructions
134    may be called upon the result type.
135
136    Custom serializers and deserializers are specified at the dialect level,
137    that is using :func:`.create_engine`.  The reason for this is that when
138    using psycopg2, the DBAPI only allows serializers at the per-cursor
139    or per-connection level.   E.g.::
140
141        engine = create_engine("postgresql://scott:tiger@localhost/test",
142                                json_serializer=my_serialize_fn,
143                                json_deserializer=my_deserialize_fn
144                        )
145
146    When using the psycopg2 dialect, the json_deserializer is registered
147    against the database using ``psycopg2.extras.register_default_json``.
148
149    .. seealso::
150
151        :class:`.types.JSON` - Core level JSON type
152
153        :class:`.JSONB`
154
155    """
156
157    astext_type = sqltypes.Text()
158
159    def __init__(self, none_as_null=False, astext_type=None):
160        """Construct a :class:`.JSON` type.
161
162        :param none_as_null: if True, persist the value ``None`` as a
163         SQL NULL value, not the JSON encoding of ``null``.   Note that
164         when this flag is False, the :func:`.null` construct can still
165         be used to persist a NULL value::
166
167             from sqlalchemy import null
168             conn.execute(table.insert(), data=null())
169
170         .. versionchanged:: 0.9.8 - Added ``none_as_null``, and :func:`.null`
171            is now supported in order to persist a NULL value.
172
173         .. seealso::
174
175              :attr:`.JSON.NULL`
176
177        :param astext_type: the type to use for the
178         :attr:`.JSON.Comparator.astext`
179         accessor on indexed attributes.  Defaults to :class:`.types.Text`.
180
181         .. versionadded:: 1.1
182
183         """
184        super(JSON, self).__init__(none_as_null=none_as_null)
185        if astext_type is not None:
186            self.astext_type = astext_type
187
188    class Comparator(sqltypes.JSON.Comparator):
189        """Define comparison operations for :class:`.JSON`."""
190
191        @property
192        def astext(self):
193            """On an indexed expression, use the "astext" (e.g. "->>")
194            conversion when rendered in SQL.
195
196            E.g.::
197
198                select([data_table.c.data['some key'].astext])
199
200            .. seealso::
201
202                :meth:`.ColumnElement.cast`
203
204            """
205
206            if isinstance(self.expr.right.type, sqltypes.JSON.JSONPathType):
207                return self.expr.left.operate(
208                    JSONPATH_ASTEXT,
209                    self.expr.right, result_type=self.type.astext_type)
210            else:
211                return self.expr.left.operate(
212                    ASTEXT, self.expr.right, result_type=self.type.astext_type)
213
214    comparator_factory = Comparator
215
216
217colspecs[sqltypes.JSON] = JSON
218ischema_names['json'] = JSON
219
220
221class JSONB(JSON):
222    """Represent the PostgreSQL JSONB type.
223
224    The :class:`.JSONB` type stores arbitrary JSONB format data, e.g.::
225
226        data_table = Table('data_table', metadata,
227            Column('id', Integer, primary_key=True),
228            Column('data', JSONB)
229        )
230
231        with engine.connect() as conn:
232            conn.execute(
233                data_table.insert(),
234                data = {"key1": "value1", "key2": "value2"}
235            )
236
237    The :class:`.JSONB` type includes all operations provided by
238    :class:`.JSON`, including the same behaviors for indexing operations.
239    It also adds additional operators specific to JSONB, including
240    :meth:`.JSONB.Comparator.has_key`, :meth:`.JSONB.Comparator.has_all`,
241    :meth:`.JSONB.Comparator.has_any`, :meth:`.JSONB.Comparator.contains`,
242    and :meth:`.JSONB.Comparator.contained_by`.
243
244    Like the :class:`.JSON` type, the :class:`.JSONB` type does not detect
245    in-place changes when used with the ORM, unless the
246    :mod:`sqlalchemy.ext.mutable` extension is used.
247
248    Custom serializers and deserializers
249    are shared with the :class:`.JSON` class, using the ``json_serializer``
250    and ``json_deserializer`` keyword arguments.  These must be specified
251    at the dialect level using :func:`.create_engine`.  When using
252    psycopg2, the serializers are associated with the jsonb type using
253    ``psycopg2.extras.register_default_jsonb`` on a per-connection basis,
254    in the same way that ``psycopg2.extras.register_default_json`` is used
255    to register these handlers with the json type.
256
257    .. versionadded:: 0.9.7
258
259    .. seealso::
260
261        :class:`.JSON`
262
263    """
264
265    __visit_name__ = 'JSONB'
266
267    class Comparator(JSON.Comparator):
268        """Define comparison operations for :class:`.JSON`."""
269
270        def has_key(self, other):
271            """Boolean expression.  Test for presence of a key.  Note that the
272            key may be a SQLA expression.
273            """
274            return self.operate(HAS_KEY, other, result_type=sqltypes.Boolean)
275
276        def has_all(self, other):
277            """Boolean expression.  Test for presence of all keys in jsonb
278            """
279            return self.operate(HAS_ALL, other, result_type=sqltypes.Boolean)
280
281        def has_any(self, other):
282            """Boolean expression.  Test for presence of any key in jsonb
283            """
284            return self.operate(HAS_ANY, other, result_type=sqltypes.Boolean)
285
286        def contains(self, other, **kwargs):
287            """Boolean expression.  Test if keys (or array) are a superset
288            of/contained the keys of the argument jsonb expression.
289            """
290            return self.operate(CONTAINS, other, result_type=sqltypes.Boolean)
291
292        def contained_by(self, other):
293            """Boolean expression.  Test if keys are a proper subset of the
294            keys of the argument jsonb expression.
295            """
296            return self.operate(
297                CONTAINED_BY, other, result_type=sqltypes.Boolean)
298
299    comparator_factory = Comparator
300
301ischema_names['jsonb'] = JSONB
302