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