codekingpro/portable-devtools
115k
1# dialects/postgresql/json.py
2# Copyright (C) 2005-2026 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
8from __future__ import annotations
9
10from typing import Any
11from typing import Callable
12from typing import List
13from typing import Optional
14from typing import TYPE_CHECKING
15from typing import Union
16
17from .array import ARRAY
18from .array import array as _pg_array
19from .operators import ASTEXT
20from .operators import CONTAINED_BY
21from .operators import CONTAINS
22from .operators import DELETE_PATH
23from .operators import HAS_ALL
24from .operators import HAS_ANY
25from .operators import HAS_KEY
26from .operators import JSONPATH_ASTEXT
27from .operators import PATH_EXISTS
28from .operators import PATH_MATCH
29from ... import types as sqltypes
30from ...sql import cast
31from ...sql._typing import _T
32
33if TYPE_CHECKING:
34 from ...engine.interfaces import Dialect
35 from ...sql.elements import ColumnElement
36 from ...sql.operators import OperatorType
37 from ...sql.type_api import _BindProcessorType
38 from ...sql.type_api import _LiteralProcessorType
39 from ...sql.type_api import TypeEngine
40
41__all__ = ("JSON", "JSONB")
42
43
44class JSONPathType(sqltypes.JSON.JSONPathType):
45 def _processor(
46 self, dialect: Dialect, super_proc: Optional[Callable[[Any], Any]]
47 ) -> Callable[[Any], Any]:
48 def process(value: Any) -> Any:
49 if isinstance(value, str):
50 # If it's already a string assume that it's in json path
51 # format. This allows using cast with json paths literals
52 return value
53 elif value:
54 # If it's already a string assume that it's in json path
55 # format. This allows using cast with json paths literals
56 value = "{%s}" % (", ".join(map(str, value)))
57 else:
58 value = "{}"
59 if super_proc:
60 value = super_proc(value)
61 return value
62
63 return process
64
65 def bind_processor(self, dialect: Dialect) -> _BindProcessorType[Any]:
66 return self._processor(dialect, self.string_bind_processor(dialect)) # type: ignore[return-value] # noqa: E501
67
68 def literal_processor(
69 self, dialect: Dialect
70 ) -> _LiteralProcessorType[Any]:
71 return self._processor(dialect, self.string_literal_processor(dialect)) # type: ignore[return-value] # noqa: E501
72
73
74class JSONPATH(JSONPathType):
75 """JSON Path Type.
76
77 This is usually required to cast literal values to json path when using
78 json search like function, such as ``jsonb_path_query_array`` or
79 ``jsonb_path_exists``::
80
81 stmt = sa.select(
82 sa.func.jsonb_path_query_array(
83 table.c.jsonb_col, cast("$.address.id", JSONPATH)
84 )
85 )
86
87 """
88
89 __visit_name__ = "JSONPATH"
90
91
92class JSON(sqltypes.JSON):
93 """Represent the PostgreSQL JSON type.
94
95 :class:`_postgresql.JSON` is used automatically whenever the base
96 :class:`_types.JSON` datatype is used against a PostgreSQL backend,
97 however base :class:`_types.JSON` datatype does not provide Python
98 accessors for PostgreSQL-specific comparison methods such as
99 :meth:`_postgresql.JSON.Comparator.astext`; additionally, to use
100 PostgreSQL ``JSONB``, the :class:`_postgresql.JSONB` datatype should
101 be used explicitly.
102
103 .. seealso::
104
105 :class:`_types.JSON` - main documentation for the generic
106 cross-platform JSON datatype.
107
108 The operators provided by the PostgreSQL version of :class:`_types.JSON`
109 include:
110
111 * Index operations (the ``->`` operator)::
112
113 data_table.c.data["some key"]
114
115 data_table.c.data[5]
116
117 * Index operations returning text
118 (the ``->>`` operator)::
119
120 data_table.c.data["some key"].astext == "some value"
121
122 Note that equivalent functionality is available via the
123 :attr:`.JSON.Comparator.as_string` accessor.
124
125 * Index operations with CAST
126 (equivalent to ``CAST(col ->> ['some key'] AS <type>)``)::
127
128 data_table.c.data["some key"].astext.cast(Integer) == 5
129
130 Note that equivalent functionality is available via the
131 :attr:`.JSON.Comparator.as_integer` and similar accessors.
132
133 * Path index operations (the ``#>`` operator)::
134
135 data_table.c.data[("key_1", "key_2", 5, ..., "key_n")]
136
137 * Path index operations returning text (the ``#>>`` operator)::
138
139 data_table.c.data[
140 ("key_1", "key_2", 5, ..., "key_n")
141 ].astext == "some value"
142
143 Index operations return an expression object whose type defaults to
144 :class:`_types.JSON` by default,
145 so that further JSON-oriented instructions
146 may be called upon the result type.
147
148 Custom serializers and deserializers are specified at the dialect level,
149 that is using :func:`_sa.create_engine`. The reason for this is that when
150 using psycopg2, the DBAPI only allows serializers at the per-cursor
151 or per-connection level. E.g.::
152
153 engine = create_engine(
154 "postgresql+psycopg2://scott:tiger@localhost/test",
155 json_serializer=my_serialize_fn,
156 json_deserializer=my_deserialize_fn,
157 )
158
159 When using the psycopg2 dialect, the json_deserializer is registered
160 against the database using ``psycopg2.extras.register_default_json``.
161
162 .. seealso::
163
164 :class:`_types.JSON` - Core level JSON type
165
166 :class:`_postgresql.JSONB`
167
168 """ # noqa
169
170 render_bind_cast = True
171 astext_type: TypeEngine[str] = sqltypes.Text()
172
173 def __init__(
174 self,
175 none_as_null: bool = False,
176 astext_type: Optional[TypeEngine[str]] = None,
177 ):
178 """Construct a :class:`_types.JSON` type.
179
180 :param none_as_null: if True, persist the value ``None`` as a
181 SQL NULL value, not the JSON encoding of ``null``. Note that
182 when this flag is False, the :func:`.null` construct can still
183 be used to persist a NULL value::
184
185 from sqlalchemy import null
186
187 conn.execute(table.insert(), {"data": null()})
188
189 .. seealso::
190
191 :attr:`_types.JSON.NULL`
192
193 :param astext_type: the type to use for the
194 :attr:`.JSON.Comparator.astext`
195 accessor on indexed attributes. Defaults to :class:`_types.Text`.
196
197 """
198 super().__init__(none_as_null=none_as_null)
199 if astext_type is not None:
200 self.astext_type = astext_type
201
202 class Comparator(sqltypes.JSON.Comparator[_T]):
203 """Define comparison operations for :class:`_types.JSON`."""
204
205 type: JSON
206
207 @property
208 def astext(self) -> ColumnElement[str]:
209 """On an indexed expression, use the "astext" (e.g. "->>")
210 conversion when rendered in SQL.
211
212 E.g.::
213
214 select(data_table.c.data["some key"].astext)
215
216 .. seealso::
217
218 :meth:`_expression.ColumnElement.cast`
219
220 """
221 if isinstance(self.expr.right.type, sqltypes.JSON.JSONPathType):
222 return self.expr.left.operate( # type: ignore[no-any-return]
223 JSONPATH_ASTEXT,
224 self.expr.right,
225 result_type=self.type.astext_type,
226 )
227 else:
228 return self.expr.left.operate( # type: ignore[no-any-return]
229 ASTEXT, self.expr.right, result_type=self.type.astext_type
230 )
231
232 comparator_factory = Comparator
233
234
235class JSONB(JSON):
236 """Represent the PostgreSQL JSONB type.
237
238 The :class:`_postgresql.JSONB` type stores arbitrary JSONB format data,
239 e.g.::
240
241 data_table = Table(
242 "data_table",
243 metadata,
244 Column("id", Integer, primary_key=True),
245 Column("data", JSONB),
246 )
247
248 with engine.connect() as conn:
249 conn.execute(
250 data_table.insert(), data={"key1": "value1", "key2": "value2"}
251 )
252
253 The :class:`_postgresql.JSONB` type includes all operations provided by
254 :class:`_types.JSON`, including the same behaviors for indexing
255 operations.
256 It also adds additional operators specific to JSONB, including
257 :meth:`.JSONB.Comparator.has_key`, :meth:`.JSONB.Comparator.has_all`,
258 :meth:`.JSONB.Comparator.has_any`, :meth:`.JSONB.Comparator.contains`,
259 :meth:`.JSONB.Comparator.contained_by`,
260 :meth:`.JSONB.Comparator.delete_path`,
261 :meth:`.JSONB.Comparator.path_exists` and
262 :meth:`.JSONB.Comparator.path_match`.
263
264 Like the :class:`_types.JSON` type, the :class:`_postgresql.JSONB`
265 type does not detect
266 in-place changes when used with the ORM, unless the
267 :mod:`sqlalchemy.ext.mutable` extension is used.
268
269 Custom serializers and deserializers
270 are shared with the :class:`_types.JSON` class,
271 using the ``json_serializer``
272 and ``json_deserializer`` keyword arguments. These must be specified
273 at the dialect level using :func:`_sa.create_engine`. When using
274 psycopg2, the serializers are associated with the jsonb type using
275 ``psycopg2.extras.register_default_jsonb`` on a per-connection basis,
276 in the same way that ``psycopg2.extras.register_default_json`` is used
277 to register these handlers with the json type.
278
279 .. seealso::
280
281 :class:`_types.JSON`
282
283 .. warning::
284
285 **For applications that have indexes against JSONB subscript
286 expressions**
287
288 SQLAlchemy 2.0.42 made a change in how the subscript operation for
289 :class:`.JSONB` is rendered, from ``-> 'element'`` to ``['element']``,
290 for PostgreSQL versions greater than 14. This change caused an
291 unintended side effect for indexes that were created against
292 expressions that use subscript notation, e.g.
293 ``Index("ix_entity_json_ab_text", data["a"]["b"].astext)``. If these
294 indexes were generated with the older syntax e.g. ``((entity.data ->
295 'a') ->> 'b')``, they will not be used by the PostgreSQL query planner
296 when a query is made using SQLAlchemy 2.0.42 or higher on PostgreSQL
297 versions 14 or higher. This occurs because the new text will resemble
298 ``(entity.data['a'] ->> 'b')`` which will fail to produce the exact
299 textual syntax match required by the PostgreSQL query planner.
300 Therefore, for users upgrading to SQLAlchemy 2.0.42 or higher, existing
301 indexes that were created against :class:`.JSONB` expressions that use
302 subscripting would need to be dropped and re-created in order for them
303 to work with the new query syntax, e.g. an expression like
304 ``((entity.data -> 'a') ->> 'b')`` would become ``(entity.data['a'] ->>
305 'b')``.
306
307 .. seealso::
308
309 :ticket:`12868` - discussion of this issue
310
311 """
312
313 __visit_name__ = "JSONB"
314
315 def coerce_compared_value(
316 self, op: Optional[OperatorType], value: Any
317 ) -> TypeEngine[Any]:
318 if op in (PATH_MATCH, PATH_EXISTS):
319 return JSON.JSONPathType()
320 else:
321 return super().coerce_compared_value(op, value)
322
323 class Comparator(JSON.Comparator[_T]):
324 """Define comparison operations for :class:`_types.JSON`."""
325
326 type: JSONB
327
328 def has_key(self, other: Any) -> ColumnElement[bool]:
329 """Boolean expression. Test for presence of a key (equivalent of
330 the ``?`` operator). Note that the key may be a SQLA expression.
331 """
332 return self.operate(HAS_KEY, other, result_type=sqltypes.Boolean)
333
334 def has_all(self, other: Any) -> ColumnElement[bool]:
335 """Boolean expression. Test for presence of all keys in jsonb
336 (equivalent of the ``?&`` operator)
337 """
338 return self.operate(HAS_ALL, other, result_type=sqltypes.Boolean)
339
340 def has_any(self, other: Any) -> ColumnElement[bool]:
341 """Boolean expression. Test for presence of any key in jsonb
342 (equivalent of the ``?|`` operator)
343 """
344 return self.operate(HAS_ANY, other, result_type=sqltypes.Boolean)
345
346 def contains(self, other: Any, **kwargs: Any) -> ColumnElement[bool]:
347 """Boolean expression. Test if keys (or array) are a superset
348 of/contained the keys of the argument jsonb expression
349 (equivalent of the ``@>`` operator).
350
351 kwargs may be ignored by this operator but are required for API
352 conformance.
353 """
354 return self.operate(CONTAINS, other, result_type=sqltypes.Boolean)
355
356 def contained_by(self, other: Any) -> ColumnElement[bool]:
357 """Boolean expression. Test if keys are a proper subset of the
358 keys of the argument jsonb expression
359 (equivalent of the ``<@`` operator).
360 """
361 return self.operate(
362 CONTAINED_BY, other, result_type=sqltypes.Boolean
363 )
364
365 def delete_path(
366 self, array: Union[List[str], _pg_array[str]]
367 ) -> ColumnElement[JSONB]:
368 """JSONB expression. Deletes field or array element specified in
369 the argument array (equivalent of the ``#-`` operator).
370
371 The input may be a list of strings that will be coerced to an
372 ``ARRAY`` or an instance of :meth:`_postgres.array`.
373
374 .. versionadded:: 2.0
375 """
376 if not isinstance(array, _pg_array):
377 array = _pg_array(array)
378 right_side = cast(array, ARRAY(sqltypes.TEXT))
379 return self.operate(DELETE_PATH, right_side, result_type=JSONB)
380
381 def path_exists(self, other: Any) -> ColumnElement[bool]:
382 """Boolean expression. Test for presence of item given by the
383 argument JSONPath expression (equivalent of the ``@?`` operator).
384
385 .. versionadded:: 2.0
386 """
387 return self.operate(
388 PATH_EXISTS, other, result_type=sqltypes.Boolean
389 )
390
391 def path_match(self, other: Any) -> ColumnElement[bool]:
392 """Boolean expression. Test if JSONPath predicate given by the
393 argument JSONPath expression matches
394 (equivalent of the ``@@`` operator).
395
396 Only the first item of the result is taken into account.
397
398 .. versionadded:: 2.0
399 """
400 return self.operate(
401 PATH_MATCH, other, result_type=sqltypes.Boolean
402 )
403
404 comparator_factory = Comparator
405