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

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 # Still need to process through super_proc for proper escaping 

53 if super_proc: 

54 value = super_proc(value) 

55 return value 

56 elif value: 

57 # If it's already a string assume that it's in json path 

58 # format. This allows using cast with json paths literals 

59 value = "{%s}" % (", ".join(map(str, value))) 

60 else: 

61 value = "{}" 

62 if super_proc: 

63 value = super_proc(value) 

64 return value 

65 

66 return process 

67 

68 def bind_processor(self, dialect: Dialect) -> _BindProcessorType[Any]: 

69 return self._processor(dialect, self.string_bind_processor(dialect)) # type: ignore[return-value] # noqa: E501 

70 

71 def literal_processor( 

72 self, dialect: Dialect 

73 ) -> _LiteralProcessorType[Any]: 

74 return self._processor(dialect, self.string_literal_processor(dialect)) # type: ignore[return-value] # noqa: E501 

75 

76 

77class JSONPATH(JSONPathType): 

78 """JSON Path Type. 

79 

80 This is usually required to cast literal values to json path when using 

81 json search like function, such as ``jsonb_path_query_array`` or 

82 ``jsonb_path_exists``:: 

83 

84 stmt = sa.select( 

85 sa.func.jsonb_path_query_array( 

86 table.c.jsonb_col, cast("$.address.id", JSONPATH) 

87 ) 

88 ) 

89 

90 """ 

91 

92 __visit_name__ = "JSONPATH" 

93 

94 

95class JSON(sqltypes.JSON): 

96 """Represent the PostgreSQL JSON type. 

97 

98 :class:`_postgresql.JSON` is used automatically whenever the base 

99 :class:`_types.JSON` datatype is used against a PostgreSQL backend, 

100 however base :class:`_types.JSON` datatype does not provide Python 

101 accessors for PostgreSQL-specific comparison methods such as 

102 :meth:`_postgresql.JSON.Comparator.astext`; additionally, to use 

103 PostgreSQL ``JSONB``, the :class:`_postgresql.JSONB` datatype should 

104 be used explicitly. 

105 

106 .. seealso:: 

107 

108 :class:`_types.JSON` - main documentation for the generic 

109 cross-platform JSON datatype. 

110 

111 The operators provided by the PostgreSQL version of :class:`_types.JSON` 

112 include: 

113 

114 * Index operations (the ``->`` operator):: 

115 

116 data_table.c.data["some key"] 

117 

118 data_table.c.data[5] 

119 

120 * Index operations returning text 

121 (the ``->>`` operator):: 

122 

123 data_table.c.data["some key"].astext == "some value" 

124 

125 Note that equivalent functionality is available via the 

126 :attr:`.JSON.Comparator.as_string` accessor. 

127 

128 * Index operations with CAST 

129 (equivalent to ``CAST(col ->> ['some key'] AS <type>)``):: 

130 

131 data_table.c.data["some key"].astext.cast(Integer) == 5 

132 

133 Note that equivalent functionality is available via the 

134 :attr:`.JSON.Comparator.as_integer` and similar accessors. 

135 

136 * Path index operations (the ``#>`` operator):: 

137 

138 data_table.c.data[("key_1", "key_2", 5, ..., "key_n")] 

139 

140 * Path index operations returning text (the ``#>>`` operator):: 

141 

142 data_table.c.data[ 

143 ("key_1", "key_2", 5, ..., "key_n") 

144 ].astext == "some value" 

145 

146 Index operations return an expression object whose type defaults to 

147 :class:`_types.JSON` by default, 

148 so that further JSON-oriented instructions 

149 may be called upon the result type. 

150 

151 Custom serializers and deserializers are specified at the dialect level, 

152 that is using :func:`_sa.create_engine`. The reason for this is that when 

153 using psycopg2, the DBAPI only allows serializers at the per-cursor 

154 or per-connection level. E.g.:: 

155 

156 engine = create_engine( 

157 "postgresql+psycopg2://scott:tiger@localhost/test", 

158 json_serializer=my_serialize_fn, 

159 json_deserializer=my_deserialize_fn, 

160 ) 

161 

162 When using the psycopg2 dialect, the json_deserializer is registered 

163 against the database using ``psycopg2.extras.register_default_json``. 

164 

165 .. seealso:: 

166 

167 :class:`_types.JSON` - Core level JSON type 

168 

169 :class:`_postgresql.JSONB` 

170 

171 """ # noqa 

172 

173 render_bind_cast = True 

174 astext_type: TypeEngine[str] = sqltypes.Text() 

175 

176 def __init__( 

177 self, 

178 none_as_null: bool = False, 

179 astext_type: Optional[TypeEngine[str]] = None, 

180 ): 

181 """Construct a :class:`_types.JSON` type. 

182 

183 :param none_as_null: if True, persist the value ``None`` as a 

184 SQL NULL value, not the JSON encoding of ``null``. Note that 

185 when this flag is False, the :func:`.null` construct can still 

186 be used to persist a NULL value:: 

187 

188 from sqlalchemy import null 

189 

190 conn.execute(table.insert(), {"data": null()}) 

191 

192 .. seealso:: 

193 

194 :attr:`_types.JSON.NULL` 

195 

196 :param astext_type: the type to use for the 

197 :attr:`.JSON.Comparator.astext` 

198 accessor on indexed attributes. Defaults to :class:`_types.Text`. 

199 

200 """ 

201 super().__init__(none_as_null=none_as_null) 

202 if astext_type is not None: 

203 self.astext_type = astext_type 

204 

205 class Comparator(sqltypes.JSON.Comparator[_T]): 

206 """Define comparison operations for :class:`_types.JSON`.""" 

207 

208 type: JSON 

209 

210 @property 

211 def astext(self) -> ColumnElement[str]: 

212 """On an indexed expression, use the "astext" (e.g. "->>") 

213 conversion when rendered in SQL. 

214 

215 E.g.:: 

216 

217 select(data_table.c.data["some key"].astext) 

218 

219 .. seealso:: 

220 

221 :meth:`_expression.ColumnElement.cast` 

222 

223 """ 

224 if isinstance(self.expr.right.type, sqltypes.JSON.JSONPathType): 

225 return self.expr.left.operate( # type: ignore[no-any-return] 

226 JSONPATH_ASTEXT, 

227 self.expr.right, 

228 result_type=self.type.astext_type, 

229 ) 

230 else: 

231 return self.expr.left.operate( # type: ignore[no-any-return] 

232 ASTEXT, self.expr.right, result_type=self.type.astext_type 

233 ) 

234 

235 comparator_factory = Comparator 

236 

237 

238class JSONB(JSON): 

239 """Represent the PostgreSQL JSONB type. 

240 

241 The :class:`_postgresql.JSONB` type stores arbitrary JSONB format data, 

242 e.g.:: 

243 

244 data_table = Table( 

245 "data_table", 

246 metadata, 

247 Column("id", Integer, primary_key=True), 

248 Column("data", JSONB), 

249 ) 

250 

251 with engine.connect() as conn: 

252 conn.execute( 

253 data_table.insert(), data={"key1": "value1", "key2": "value2"} 

254 ) 

255 

256 The :class:`_postgresql.JSONB` type includes all operations provided by 

257 :class:`_types.JSON`, including the same behaviors for indexing 

258 operations. 

259 It also adds additional operators specific to JSONB, including 

260 :meth:`.JSONB.Comparator.has_key`, :meth:`.JSONB.Comparator.has_all`, 

261 :meth:`.JSONB.Comparator.has_any`, :meth:`.JSONB.Comparator.contains`, 

262 :meth:`.JSONB.Comparator.contained_by`, 

263 :meth:`.JSONB.Comparator.delete_path`, 

264 :meth:`.JSONB.Comparator.path_exists` and 

265 :meth:`.JSONB.Comparator.path_match`. 

266 

267 Like the :class:`_types.JSON` type, the :class:`_postgresql.JSONB` 

268 type does not detect 

269 in-place changes when used with the ORM, unless the 

270 :mod:`sqlalchemy.ext.mutable` extension is used. 

271 

272 Custom serializers and deserializers 

273 are shared with the :class:`_types.JSON` class, 

274 using the ``json_serializer`` 

275 and ``json_deserializer`` keyword arguments. These must be specified 

276 at the dialect level using :func:`_sa.create_engine`. When using 

277 psycopg2, the serializers are associated with the jsonb type using 

278 ``psycopg2.extras.register_default_jsonb`` on a per-connection basis, 

279 in the same way that ``psycopg2.extras.register_default_json`` is used 

280 to register these handlers with the json type. 

281 

282 .. seealso:: 

283 

284 :class:`_types.JSON` 

285 

286 .. warning:: 

287 

288 **For applications that have indexes against JSONB subscript 

289 expressions** 

290 

291 SQLAlchemy 2.0.42 made a change in how the subscript operation for 

292 :class:`.JSONB` is rendered, from ``-> 'element'`` to ``['element']``, 

293 for PostgreSQL versions greater than 14. This change caused an 

294 unintended side effect for indexes that were created against 

295 expressions that use subscript notation, e.g. 

296 ``Index("ix_entity_json_ab_text", data["a"]["b"].astext)``. If these 

297 indexes were generated with the older syntax e.g. ``((entity.data -> 

298 'a') ->> 'b')``, they will not be used by the PostgreSQL query planner 

299 when a query is made using SQLAlchemy 2.0.42 or higher on PostgreSQL 

300 versions 14 or higher. This occurs because the new text will resemble 

301 ``(entity.data['a'] ->> 'b')`` which will fail to produce the exact 

302 textual syntax match required by the PostgreSQL query planner. 

303 Therefore, for users upgrading to SQLAlchemy 2.0.42 or higher, existing 

304 indexes that were created against :class:`.JSONB` expressions that use 

305 subscripting would need to be dropped and re-created in order for them 

306 to work with the new query syntax, e.g. an expression like 

307 ``((entity.data -> 'a') ->> 'b')`` would become ``(entity.data['a'] ->> 

308 'b')``. 

309 

310 .. seealso:: 

311 

312 :ticket:`12868` - discussion of this issue 

313 

314 """ 

315 

316 __visit_name__ = "JSONB" 

317 

318 def coerce_compared_value( 

319 self, op: Optional[OperatorType], value: Any 

320 ) -> TypeEngine[Any]: 

321 if op in (PATH_MATCH, PATH_EXISTS): 

322 return JSON.JSONPathType() 

323 else: 

324 return super().coerce_compared_value(op, value) 

325 

326 class Comparator(JSON.Comparator[_T]): 

327 """Define comparison operations for :class:`_types.JSON`.""" 

328 

329 type: JSONB 

330 

331 def has_key(self, other: Any) -> ColumnElement[bool]: 

332 """Boolean expression. Test for presence of a key (equivalent of 

333 the ``?`` operator). Note that the key may be a SQLA expression. 

334 """ 

335 return self.operate(HAS_KEY, other, result_type=sqltypes.Boolean) 

336 

337 def has_all(self, other: Any) -> ColumnElement[bool]: 

338 """Boolean expression. Test for presence of all keys in jsonb 

339 (equivalent of the ``?&`` operator) 

340 """ 

341 return self.operate(HAS_ALL, other, result_type=sqltypes.Boolean) 

342 

343 def has_any(self, other: Any) -> ColumnElement[bool]: 

344 """Boolean expression. Test for presence of any key in jsonb 

345 (equivalent of the ``?|`` operator) 

346 """ 

347 return self.operate(HAS_ANY, other, result_type=sqltypes.Boolean) 

348 

349 def contains(self, other: Any, **kwargs: Any) -> ColumnElement[bool]: 

350 """Boolean expression. Test if keys (or array) are a superset 

351 of/contained the keys of the argument jsonb expression 

352 (equivalent of the ``@>`` operator). 

353 

354 kwargs may be ignored by this operator but are required for API 

355 conformance. 

356 """ 

357 return self.operate(CONTAINS, other, result_type=sqltypes.Boolean) 

358 

359 def contained_by(self, other: Any) -> ColumnElement[bool]: 

360 """Boolean expression. Test if keys are a proper subset of the 

361 keys of the argument jsonb expression 

362 (equivalent of the ``<@`` operator). 

363 """ 

364 return self.operate( 

365 CONTAINED_BY, other, result_type=sqltypes.Boolean 

366 ) 

367 

368 def delete_path( 

369 self, array: Union[List[str], _pg_array[str]] 

370 ) -> ColumnElement[JSONB]: 

371 """JSONB expression. Deletes field or array element specified in 

372 the argument array (equivalent of the ``#-`` operator). 

373 

374 The input may be a list of strings that will be coerced to an 

375 ``ARRAY`` or an instance of :meth:`_postgres.array`. 

376 

377 .. versionadded:: 2.0 

378 """ 

379 if not isinstance(array, _pg_array): 

380 array = _pg_array(array) 

381 right_side = cast(array, ARRAY(sqltypes.TEXT)) 

382 return self.operate(DELETE_PATH, right_side, result_type=JSONB) 

383 

384 def path_exists(self, other: Any) -> ColumnElement[bool]: 

385 """Boolean expression. Test for presence of item given by the 

386 argument JSONPath expression (equivalent of the ``@?`` operator). 

387 

388 .. versionadded:: 2.0 

389 """ 

390 return self.operate( 

391 PATH_EXISTS, other, result_type=sqltypes.Boolean 

392 ) 

393 

394 def path_match(self, other: Any) -> ColumnElement[bool]: 

395 """Boolean expression. Test if JSONPath predicate given by the 

396 argument JSONPath expression matches 

397 (equivalent of the ``@@`` operator). 

398 

399 Only the first item of the result is taken into account. 

400 

401 .. versionadded:: 2.0 

402 """ 

403 return self.operate( 

404 PATH_MATCH, other, result_type=sqltypes.Boolean 

405 ) 

406 

407 comparator_factory = Comparator