1# postgresql/json.py 2# Copyright (C) 2005-2018 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: http://www.opensource.org/licenses/mit-license.php 7from __future__ import absolute_import 8 9import json 10import collections 11 12from .base import ischema_names, colspecs 13from ... import types as sqltypes 14from ...sql import operators 15from ...sql import elements 16from ... import util 17 18__all__ = ('JSON', 'JSONB') 19 20idx_precedence = operators._PRECEDENCE[operators.json_getitem_op] 21 22ASTEXT = operators.custom_op( 23 "->>", precedence=idx_precedence, natural_self_precedent=True, 24 eager_grouping=True 25) 26 27JSONPATH_ASTEXT = operators.custom_op( 28 "#>>", precedence=idx_precedence, natural_self_precedent=True, 29 eager_grouping=True 30) 31 32 33HAS_KEY = operators.custom_op( 34 "?", precedence=idx_precedence, natural_self_precedent=True, 35 eager_grouping=True 36) 37 38HAS_ALL = operators.custom_op( 39 "?&", precedence=idx_precedence, natural_self_precedent=True, 40 eager_grouping=True 41) 42 43HAS_ANY = operators.custom_op( 44 "?|", precedence=idx_precedence, natural_self_precedent=True, 45 eager_grouping=True 46) 47 48CONTAINS = operators.custom_op( 49 "@>", precedence=idx_precedence, natural_self_precedent=True, 50 eager_grouping=True 51) 52 53CONTAINED_BY = operators.custom_op( 54 "<@", precedence=idx_precedence, natural_self_precedent=True, 55 eager_grouping=True 56) 57 58 59class JSONPathType(sqltypes.JSON.JSONPathType): 60 def bind_processor(self, dialect): 61 super_proc = self.string_bind_processor(dialect) 62 63 def process(value): 64 assert isinstance(value, collections.Sequence) 65 tokens = [util.text_type(elem)for elem in value] 66 value = "{%s}" % (", ".join(tokens)) 67 if super_proc: 68 value = super_proc(value) 69 return value 70 71 return process 72 73 def literal_processor(self, dialect): 74 super_proc = self.string_literal_processor(dialect) 75 76 def process(value): 77 assert isinstance(value, collections.Sequence) 78 tokens = [util.text_type(elem)for elem in value] 79 value = "{%s}" % (", ".join(tokens)) 80 if super_proc: 81 value = super_proc(value) 82 return value 83 84 return process 85 86colspecs[sqltypes.JSON.JSONPathType] = JSONPathType 87 88 89class JSON(sqltypes.JSON): 90 """Represent the PostgreSQL JSON type. 91 92 This type is a specialization of the Core-level :class:`.types.JSON` 93 type. Be sure to read the documentation for :class:`.types.JSON` for 94 important tips regarding treatment of NULL values and ORM use. 95 96 .. versionchanged:: 1.1 :class:`.postgresql.JSON` is now a PostgreSQL- 97 specific specialization of the new :class:`.types.JSON` type. 98 99 The operators provided by the PostgreSQL version of :class:`.JSON` 100 include: 101 102 * Index operations (the ``->`` operator):: 103 104 data_table.c.data['some key'] 105 106 data_table.c.data[5] 107 108 109 * Index operations returning text (the ``->>`` operator):: 110 111 data_table.c.data['some key'].astext == 'some value' 112 113 * Index operations with CAST 114 (equivalent to ``CAST(col ->> ['some key'] AS <type>)``):: 115 116 data_table.c.data['some key'].astext.cast(Integer) == 5 117 118 * Path index operations (the ``#>`` operator):: 119 120 data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')] 121 122 * Path index operations returning text (the ``#>>`` operator):: 123 124 data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')].astext == \ 125'some value' 126 127 .. versionchanged:: 1.1 The :meth:`.ColumnElement.cast` operator on 128 JSON objects now requires that the :attr:`.JSON.Comparator.astext` 129 modifier be called explicitly, if the cast works only from a textual 130 string. 131 132 Index operations return an expression object whose type defaults to 133 :class:`.JSON` by default, so that further JSON-oriented instructions 134 may be called upon the result type. 135 136 Custom serializers and deserializers are specified at the dialect level, 137 that is using :func:`.create_engine`. The reason for this is that when 138 using psycopg2, the DBAPI only allows serializers at the per-cursor 139 or per-connection level. E.g.:: 140 141 engine = create_engine("postgresql://scott:tiger@localhost/test", 142 json_serializer=my_serialize_fn, 143 json_deserializer=my_deserialize_fn 144 ) 145 146 When using the psycopg2 dialect, the json_deserializer is registered 147 against the database using ``psycopg2.extras.register_default_json``. 148 149 .. seealso:: 150 151 :class:`.types.JSON` - Core level JSON type 152 153 :class:`.JSONB` 154 155 """ 156 157 astext_type = sqltypes.Text() 158 159 def __init__(self, none_as_null=False, astext_type=None): 160 """Construct a :class:`.JSON` type. 161 162 :param none_as_null: if True, persist the value ``None`` as a 163 SQL NULL value, not the JSON encoding of ``null``. Note that 164 when this flag is False, the :func:`.null` construct can still 165 be used to persist a NULL value:: 166 167 from sqlalchemy import null 168 conn.execute(table.insert(), data=null()) 169 170 .. versionchanged:: 0.9.8 - Added ``none_as_null``, and :func:`.null` 171 is now supported in order to persist a NULL value. 172 173 .. seealso:: 174 175 :attr:`.JSON.NULL` 176 177 :param astext_type: the type to use for the 178 :attr:`.JSON.Comparator.astext` 179 accessor on indexed attributes. Defaults to :class:`.types.Text`. 180 181 .. versionadded:: 1.1 182 183 """ 184 super(JSON, self).__init__(none_as_null=none_as_null) 185 if astext_type is not None: 186 self.astext_type = astext_type 187 188 class Comparator(sqltypes.JSON.Comparator): 189 """Define comparison operations for :class:`.JSON`.""" 190 191 @property 192 def astext(self): 193 """On an indexed expression, use the "astext" (e.g. "->>") 194 conversion when rendered in SQL. 195 196 E.g.:: 197 198 select([data_table.c.data['some key'].astext]) 199 200 .. seealso:: 201 202 :meth:`.ColumnElement.cast` 203 204 """ 205 206 if isinstance(self.expr.right.type, sqltypes.JSON.JSONPathType): 207 return self.expr.left.operate( 208 JSONPATH_ASTEXT, 209 self.expr.right, result_type=self.type.astext_type) 210 else: 211 return self.expr.left.operate( 212 ASTEXT, self.expr.right, result_type=self.type.astext_type) 213 214 comparator_factory = Comparator 215 216 217colspecs[sqltypes.JSON] = JSON 218ischema_names['json'] = JSON 219 220 221class JSONB(JSON): 222 """Represent the PostgreSQL JSONB type. 223 224 The :class:`.JSONB` type stores arbitrary JSONB format data, e.g.:: 225 226 data_table = Table('data_table', metadata, 227 Column('id', Integer, primary_key=True), 228 Column('data', JSONB) 229 ) 230 231 with engine.connect() as conn: 232 conn.execute( 233 data_table.insert(), 234 data = {"key1": "value1", "key2": "value2"} 235 ) 236 237 The :class:`.JSONB` type includes all operations provided by 238 :class:`.JSON`, including the same behaviors for indexing operations. 239 It also adds additional operators specific to JSONB, including 240 :meth:`.JSONB.Comparator.has_key`, :meth:`.JSONB.Comparator.has_all`, 241 :meth:`.JSONB.Comparator.has_any`, :meth:`.JSONB.Comparator.contains`, 242 and :meth:`.JSONB.Comparator.contained_by`. 243 244 Like the :class:`.JSON` type, the :class:`.JSONB` type does not detect 245 in-place changes when used with the ORM, unless the 246 :mod:`sqlalchemy.ext.mutable` extension is used. 247 248 Custom serializers and deserializers 249 are shared with the :class:`.JSON` class, using the ``json_serializer`` 250 and ``json_deserializer`` keyword arguments. These must be specified 251 at the dialect level using :func:`.create_engine`. When using 252 psycopg2, the serializers are associated with the jsonb type using 253 ``psycopg2.extras.register_default_jsonb`` on a per-connection basis, 254 in the same way that ``psycopg2.extras.register_default_json`` is used 255 to register these handlers with the json type. 256 257 .. versionadded:: 0.9.7 258 259 .. seealso:: 260 261 :class:`.JSON` 262 263 """ 264 265 __visit_name__ = 'JSONB' 266 267 class Comparator(JSON.Comparator): 268 """Define comparison operations for :class:`.JSON`.""" 269 270 def has_key(self, other): 271 """Boolean expression. Test for presence of a key. Note that the 272 key may be a SQLA expression. 273 """ 274 return self.operate(HAS_KEY, other, result_type=sqltypes.Boolean) 275 276 def has_all(self, other): 277 """Boolean expression. Test for presence of all keys in jsonb 278 """ 279 return self.operate(HAS_ALL, other, result_type=sqltypes.Boolean) 280 281 def has_any(self, other): 282 """Boolean expression. Test for presence of any key in jsonb 283 """ 284 return self.operate(HAS_ANY, other, result_type=sqltypes.Boolean) 285 286 def contains(self, other, **kwargs): 287 """Boolean expression. Test if keys (or array) are a superset 288 of/contained the keys of the argument jsonb expression. 289 """ 290 return self.operate(CONTAINS, other, result_type=sqltypes.Boolean) 291 292 def contained_by(self, other): 293 """Boolean expression. Test if keys are a proper subset of the 294 keys of the argument jsonb expression. 295 """ 296 return self.operate( 297 CONTAINED_BY, other, result_type=sqltypes.Boolean) 298 299 comparator_factory = Comparator 300 301ischema_names['jsonb'] = JSONB 302