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.operators import OperatorClass 

32from ...sql.sqltypes import _CT_JSON 

33from ...sql.sqltypes import _T_JSON 

34 

35if TYPE_CHECKING: 

36 from ...engine.interfaces import Dialect 

37 from ...sql.elements import ColumnElement 

38 from ...sql.operators import OperatorType 

39 from ...sql.type_api import _BindProcessorType 

40 from ...sql.type_api import _LiteralProcessorType 

41 from ...sql.type_api import TypeEngine 

42 

43__all__ = ("JSON", "JSONB") 

44 

45 

46class JSONPathType(sqltypes.JSON.JSONPathType): 

47 def _processor( 

48 self, dialect: Dialect, super_proc: Optional[Callable[[Any], Any]] 

49 ) -> Callable[[Any], Any]: 

50 def process(value: Any) -> Any: 

51 if isinstance(value, str): 

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

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

54 # Still need to process through super_proc for proper escaping 

55 if super_proc: 

56 value = super_proc(value) 

57 return value 

58 elif value: 

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

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

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

62 else: 

63 value = "{}" 

64 if super_proc: 

65 value = super_proc(value) 

66 return value 

67 

68 return process 

69 

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

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

72 

73 def literal_processor( 

74 self, dialect: Dialect 

75 ) -> _LiteralProcessorType[Any]: 

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

77 

78 

79class JSONPATH(JSONPathType): 

80 """JSON Path Type. 

81 

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

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

84 ``jsonb_path_exists``:: 

85 

86 stmt = sa.select( 

87 sa.func.jsonb_path_query_array( 

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

89 ) 

90 ) 

91 

92 """ 

93 

94 __visit_name__ = "JSONPATH" 

95 

96 

97class JSON(sqltypes.JSON[_T_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 * Index operations returning text 

123 (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[ 

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

146 ].astext == "some value" 

147 

148 Index operations return an expression object whose type defaults to 

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

150 so that further JSON-oriented instructions 

151 may be called upon the result type. 

152 

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

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

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

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

157 

158 engine = create_engine( 

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

160 json_serializer=my_serialize_fn, 

161 json_deserializer=my_deserialize_fn, 

162 ) 

163 

164 When using the psycopg2 dialect, the json_deserializer is registered 

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

166 

167 .. seealso:: 

168 

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

170 

171 :class:`_postgresql.JSONB` 

172 

173 """ # noqa 

174 

175 render_bind_cast = True 

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

177 

178 def __init__( 

179 self, 

180 none_as_null: bool = False, 

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

182 ): 

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

184 

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

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

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

188 be used to persist a NULL value:: 

189 

190 from sqlalchemy import null 

191 

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

193 

194 .. seealso:: 

195 

196 :attr:`_types.JSON.NULL` 

197 

198 :param astext_type: the type to use for the 

199 :attr:`.JSON.Comparator.astext` 

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

201 

202 """ 

203 super().__init__(none_as_null=none_as_null) 

204 if astext_type is not None: 

205 self.astext_type = astext_type 

206 

207 class Comparator(sqltypes.JSON.Comparator[_CT_JSON]): 

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

209 

210 type: JSON[_CT_JSON] 

211 

212 @property 

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

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

215 conversion when rendered in SQL. 

216 

217 E.g.:: 

218 

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

220 

221 .. seealso:: 

222 

223 :meth:`_expression.ColumnElement.cast` 

224 

225 """ 

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

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

228 JSONPATH_ASTEXT, 

229 self.expr.right, 

230 result_type=self.type.astext_type, 

231 ) 

232 else: 

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

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

235 ) 

236 

237 comparator_factory = Comparator 

238 

239 

240class JSONB(JSON[_T_JSON]): 

241 """Represent the PostgreSQL JSONB type. 

242 

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

244 e.g.:: 

245 

246 data_table = Table( 

247 "data_table", 

248 metadata, 

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

250 Column("data", JSONB), 

251 ) 

252 

253 with engine.connect() as conn: 

254 conn.execute( 

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

256 ) 

257 

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

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

260 operations. 

261 It also adds additional operators specific to JSONB, including 

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

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

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

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

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

267 :meth:`.JSONB.Comparator.path_match`. 

268 

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

270 type does not detect 

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

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

273 

274 Custom serializers and deserializers 

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

276 using the ``json_serializer`` 

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

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

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

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

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

282 to register these handlers with the json type. 

283 

284 .. seealso:: 

285 

286 :class:`_types.JSON` 

287 

288 .. warning:: 

289 

290 **For applications that have indexes against JSONB subscript 

291 expressions** 

292 

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

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

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

296 unintended side effect for indexes that were created against 

297 expressions that use subscript notation, e.g. 

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

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

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

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

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

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

304 textual syntax match required by the PostgreSQL query planner. 

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

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

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

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

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

310 'b')``. 

311 

312 .. seealso:: 

313 

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

315 

316 """ 

317 

318 __visit_name__ = "JSONB" 

319 

320 operator_classes = OperatorClass.JSON | OperatorClass.CONCATENABLE 

321 

322 def coerce_compared_value( 

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

324 ) -> TypeEngine[Any]: 

325 if op in (PATH_MATCH, PATH_EXISTS): 

326 return JSON.JSONPathType() 

327 else: 

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

329 

330 class Comparator(JSON.Comparator[_CT_JSON]): 

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

332 

333 type: JSONB[_CT_JSON] 

334 

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

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

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

338 """ 

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

340 

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

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

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

344 """ 

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

346 

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

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

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

350 """ 

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

352 

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

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

355 of/contained the keys of the argument jsonb expression 

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

357 

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

359 conformance. 

360 """ 

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

362 

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

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

365 keys of the argument jsonb expression 

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

367 """ 

368 return self.operate( 

369 CONTAINED_BY, other, result_type=sqltypes.Boolean 

370 ) 

371 

372 def delete_path( 

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

374 ) -> ColumnElement[_CT_JSON]: 

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

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

377 

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

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

380 

381 .. versionadded:: 2.0 

382 """ 

383 if not isinstance(array, _pg_array): 

384 array = _pg_array(array) 

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

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

387 

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

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

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

391 

392 .. versionadded:: 2.0 

393 """ 

394 return self.operate( 

395 PATH_EXISTS, other, result_type=sqltypes.Boolean 

396 ) 

397 

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

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

400 argument JSONPath expression matches 

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

402 

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

404 

405 .. versionadded:: 2.0 

406 """ 

407 return self.operate( 

408 PATH_MATCH, other, result_type=sqltypes.Boolean 

409 ) 

410 

411 comparator_factory = Comparator