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