Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/json.py: 58%

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