From 01cc8fbacce8f571a47a3617b913e579bd666f97 Mon Sep 17 00:00:00 2001 From: Damian Dimmich Date: Tue, 24 Jun 2014 11:23:21 +0400 Subject: initial support for JSONB - this only allows you to define the JSONB datatype - this does not add any of the additional support for querying/indexing yet. --- lib/sqlalchemy/dialects/postgresql/json.py | 113 ++++++++++++++++++++++++++++- 1 file changed, 112 insertions(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql/json.py') diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py index 2e29185e8..37196dfb1 100644 --- a/lib/sqlalchemy/dialects/postgresql/json.py +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -14,7 +14,7 @@ from ... import sql from ...sql import elements from ... import util -__all__ = ('JSON', 'JSONElement') +__all__ = ('JSON', 'JSONElement', 'JSONB') class JSONElement(elements.BinaryExpression): @@ -197,3 +197,114 @@ class JSON(sqltypes.TypeEngine): ischema_names['json'] = JSON + + + +class JSONB(sqltypes.TypeEngine): + """Represent the Postgresql JSONB type. + + The :class:`.JSONB` type stores arbitrary JSONB format data, e.g.:: + + data_table = Table('data_table', metadata, + Column('id', Integer, primary_key=True), + Column('data', JSONB) + ) + + with engine.connect() as conn: + conn.execute( + data_table.insert(), + data = {"key1": "value1", "key2": "value2"} + ) + + :class:`.JSONB` provides several operations: + + * Index operations:: + + data_table.c.data['some key'] + + * Index operations returning text (required for text comparison):: + + data_table.c.data['some key'].astext == 'some value' + + * Index operations with a built-in CAST call:: + + data_table.c.data['some key'].cast(Integer) == 5 + + * Path index operations:: + + data_table.c.data[('key_1', 'key_2', ..., 'key_n')] + + * Path index operations returning text (required for text comparison):: + + data_table.c.data[('key_1', 'key_2', ..., 'key_n')].astext == 'some value' + + Index operations return an instance of :class:`.JSONElement`, which represents + an expression such as ``column -> index``. This element then defines + methods such as :attr:`.JSONElement.astext` and :meth:`.JSONElement.cast` + for setting up type behavior. + + The :class:`.JSON` type, when used with the SQLAlchemy ORM, does not detect + in-place mutations to the structure. In order to detect these, the + :mod:`sqlalchemy.ext.mutable` extension must be used. This extension will + allow "in-place" changes to the datastructure to produce events which + will be detected by the unit of work. See the example at :class:`.HSTORE` + for a simple example involving a dictionary. + + Custom serializers and deserializers are specified at the dialect level, + that is using :func:`.create_engine`. The reason for this is that when + using psycopg2, the DBAPI only allows serializers at the per-cursor + or per-connection level. E.g.:: + + engine = create_engine("postgresql://scott:tiger@localhost/test", + json_serializer=my_serialize_fn, + json_deserializer=my_deserialize_fn + ) + + When using the psycopg2 dialect, the json_deserializer is registered + against the database using ``psycopg2.extras.register_default_json``. + + .. versionadded:: 0.9.7 + + """ + + __visit_name__ = 'JSONB' + + class comparator_factory(sqltypes.Concatenable.Comparator): + """Define comparison operations for :class:`.JSON`.""" + + def __getitem__(self, other): + """Get the value at a given key.""" + + return JSONElement(self.expr, other) + + def _adapt_expression(self, op, other_comparator): + if isinstance(op, custom_op): + if op.opstring == '->': + return op, sqltypes.Text + return sqltypes.Concatenable.Comparator.\ + _adapt_expression(self, op, other_comparator) + + def bind_processor(self, dialect): + json_serializer = dialect._json_serializer or json.dumps + if util.py2k: + encoding = dialect.encoding + def process(value): + return json_serializer(value).encode(encoding) + else: + def process(value): + return json_serializer(value) + return process + + def result_processor(self, dialect, coltype): + json_deserializer = dialect._json_deserializer or json.loads + if util.py2k: + encoding = dialect.encoding + def process(value): + return json_deserializer(value.decode(encoding)) + else: + def process(value): + return json_deserializer(value) + return process + + +ischema_names['jsonb'] = JSONB \ No newline at end of file -- cgit v1.2.1 From 4e5fcee975040d6d82baceb8e0535a548411faa6 Mon Sep 17 00:00:00 2001 From: Damian Dimmich Date: Sat, 28 Jun 2014 22:47:20 +0400 Subject: add has_key & contains operators for jsonb (ported over from hstore) --- lib/sqlalchemy/dialects/postgresql/json.py | 38 +++++++++++++----------------- 1 file changed, 17 insertions(+), 21 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/json.py') diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py index 37196dfb1..262ec20bd 100644 --- a/lib/sqlalchemy/dialects/postgresql/json.py +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -200,7 +200,7 @@ ischema_names['json'] = JSON -class JSONB(sqltypes.TypeEngine): +class JSONB(JSON): """Represent the Postgresql JSONB type. The :class:`.JSONB` type stores arbitrary JSONB format data, e.g.:: @@ -268,6 +268,7 @@ class JSONB(sqltypes.TypeEngine): """ __visit_name__ = 'JSONB' + hashable = False class comparator_factory(sqltypes.Concatenable.Comparator): """Define comparison operations for :class:`.JSON`.""" @@ -279,32 +280,27 @@ class JSONB(sqltypes.TypeEngine): def _adapt_expression(self, op, other_comparator): if isinstance(op, custom_op): + if op.opstring in ['?', '?&', '?|', '@>', '<@']: + return op, sqltypes.Boolean if op.opstring == '->': return op, sqltypes.Text return sqltypes.Concatenable.Comparator.\ _adapt_expression(self, op, other_comparator) - def bind_processor(self, dialect): - json_serializer = dialect._json_serializer or json.dumps - if util.py2k: - encoding = dialect.encoding - def process(value): - return json_serializer(value).encode(encoding) - else: - def process(value): - return json_serializer(value) - return process + def has_key(self, other): + """Boolean expression. Test for presence of a key. Note that the + key may be a SQLA expression. + """ + return self.expr.op('?')(other) + + def contains(self, other, **kwargs): + """Boolean expression. Test if keys (or array) are a superset of/contained + the keys of the argument jsonb expression. + """ + return self.expr.op('@>')(other) + + - def result_processor(self, dialect, coltype): - json_deserializer = dialect._json_deserializer or json.loads - if util.py2k: - encoding = dialect.encoding - def process(value): - return json_deserializer(value.decode(encoding)) - else: - def process(value): - return json_deserializer(value) - return process ischema_names['jsonb'] = JSONB \ No newline at end of file -- cgit v1.2.1 From 4eca136e0914252a291046d8c2ed281fe6c56d94 Mon Sep 17 00:00:00 2001 From: Damian Dimmich Date: Sat, 28 Jun 2014 23:11:03 +0400 Subject: minor cleanup of the jsonb - had extraneous operators that where copied from hstore that don't apply. Add tests for ? and @> operators. --- lib/sqlalchemy/dialects/postgresql/json.py | 6 +----- 1 file changed, 1 insertion(+), 5 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/json.py') diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py index 262ec20bd..183cb2695 100644 --- a/lib/sqlalchemy/dialects/postgresql/json.py +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -280,7 +280,7 @@ class JSONB(JSON): def _adapt_expression(self, op, other_comparator): if isinstance(op, custom_op): - if op.opstring in ['?', '?&', '?|', '@>', '<@']: + if op.opstring in ['?', '@>']: return op, sqltypes.Boolean if op.opstring == '->': return op, sqltypes.Text @@ -299,8 +299,4 @@ class JSONB(JSON): """ return self.expr.op('@>')(other) - - - - ischema_names['jsonb'] = JSONB \ No newline at end of file -- cgit v1.2.1 From ceeee81017e5fb0ac03f4a102ffd6cce418f0b05 Mon Sep 17 00:00:00 2001 From: Damian Dimmich Date: Tue, 1 Jul 2014 13:24:30 +0400 Subject: jsonb support for <@, ?| and ?& added. need to see if equality already works. --- lib/sqlalchemy/dialects/postgresql/json.py | 19 ++++++++++++++++++- 1 file changed, 18 insertions(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql/json.py') diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py index 183cb2695..d19dbe118 100644 --- a/lib/sqlalchemy/dialects/postgresql/json.py +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -279,8 +279,9 @@ class JSONB(JSON): return JSONElement(self.expr, other) def _adapt_expression(self, op, other_comparator): + # How does one do equality?? jsonb also has "=" eg. '[1,2,3]'::jsonb = '[1,2,3]'::jsonb if isinstance(op, custom_op): - if op.opstring in ['?', '@>']: + if op.opstring in ['?', '?&', '?|', '@>', '<@']: return op, sqltypes.Boolean if op.opstring == '->': return op, sqltypes.Text @@ -293,10 +294,26 @@ class JSONB(JSON): """ return self.expr.op('?')(other) + def has_all(self, other): + """Boolean expression. Test for presence of all keys in jsonb + """ + return self.expr.op('?&')(other) + + def has_any(self, other): + """Boolean expression. Test for presence of any key in jsonb + """ + return self.expr.op('?|')(other) + def contains(self, other, **kwargs): """Boolean expression. Test if keys (or array) are a superset of/contained the keys of the argument jsonb expression. """ return self.expr.op('@>')(other) + def contained_by(self, other): + """Boolean expression. Test if keys are a proper subset of the + keys of the argument jsonb expression. + """ + return self.expr.op('<@')(other) + ischema_names['jsonb'] = JSONB \ No newline at end of file -- cgit v1.2.1