diff options
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/pgjson.py | 16 | ||||
| -rw-r--r-- | test/dialect/postgresql/test_types.py | 40 |
2 files changed, 51 insertions, 5 deletions
diff --git a/lib/sqlalchemy/dialects/postgresql/pgjson.py b/lib/sqlalchemy/dialects/postgresql/pgjson.py index 161fe83fa..b41446dae 100644 --- a/lib/sqlalchemy/dialects/postgresql/pgjson.py +++ b/lib/sqlalchemy/dialects/postgresql/pgjson.py @@ -39,7 +39,7 @@ class JSON(sqltypes.TypeEngine): * Path Index operations:: - data_table.c.data.get_path('{key_1, key_2, ..., key_n}'] + data_table.c.data.get_path("'{key_1, key_2, ..., key_n}'"] Please be aware that when used with the SQLAlchemy ORM, you will need to replace the JSON object present on an attribute with a new object in order @@ -71,11 +71,23 @@ class JSON(sqltypes.TypeEngine): # The only downside to this is that you cannot dereference more # than one level deep in json structures, though comparator # support for multi-level dereference is lacking anyhow. + return self.expr.op('->', precedence=5)(other) + + def get_item_as_text(self, other): + """Text expression. Get the value at the given key as text. Use + this when you need to cast the type of the returned value.""" return self.expr.op('->>', precedence=5)(other) def get_path(self, other): - """Text expression. Get the value at a given path. Paths are of + """Text expression. Get the value at a given path. Paths are of the form {key_1, key_2, ..., key_n}.""" + return self.expr.op('#>', precedence=5)(other) + + def get_path_as_text(self, other): + """Text expression. Get the value at a given path, as text. + Paths are of the form '{key_1, key_2, ..., key_n}' (quotes are + required). Use this when you need to cast the type of the + returned value.""" return self.expr.op('#>>', precedence=5)(other) def _adapt_expression(self, op, other_comparator): diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py index 114b36f8f..c7a973e4e 100644 --- a/test/dialect/postgresql/test_types.py +++ b/test/dialect/postgresql/test_types.py @@ -1713,19 +1713,31 @@ class JSONTest(fixtures.TestBase): def test_where_getitem(self): self._test_where( self.jsoncol['bar'] == None, - "(test_table.test_column ->> %(test_column_1)s) IS NULL" + "(test_table.test_column -> %(test_column_1)s) IS NULL" ) def test_where_path(self): self._test_where( self.jsoncol.get_path('{"foo", 1}') == None, + "(test_table.test_column #> %(test_column_1)s) IS NULL" + ) + + def test_where_getitem_as_text(self): + self._test_where( + self.jsoncol.get_item_as_text('bar') == None, + "(test_table.test_column ->> %(test_column_1)s) IS NULL" + ) + + def test_where_path_as_text(self): + self._test_where( + self.jsoncol.get_path_as_text('{"foo", 1}') == None, "(test_table.test_column #>> %(test_column_1)s) IS NULL" ) def test_cols_get(self): self._test_cols( self.jsoncol['foo'], - "test_table.test_column ->> %(test_column_1)s AS anon_1", + "test_table.test_column -> %(test_column_1)s AS anon_1", True ) @@ -1800,9 +1812,31 @@ class JSONRoundTripTest(fixtures.TablesTest): self._fixture_data(engine) self._test_criterion(engine) + def test_path_query(self): + engine = testing.db + self._fixture_data(engine) + data_table = self.tables.data_table + result = engine.execute( + select([data_table.c.data]).where( + data_table.c.data.get_path_as_text('{k1}') == 'r3v1' + ) + ).first() + eq_(result, ({'k1': 'r3v1', 'k2': 'r3v2'},)) + + def test_query_returned_as_text(self): + engine = testing.db + self._fixture_data(engine) + data_table = self.tables.data_table + result = engine.execute( + select([data_table.c.data.get_item_as_text('k1')]) + ).first() + assert isinstance(result[0], basestring) + def _test_criterion(self, engine): data_table = self.tables.data_table result = engine.execute( - select([data_table.c.data]).where(data_table.c.data['k1'] == 'r3v1') + select([data_table.c.data]).where( + data_table.c.data.get_item_as_text('k1') == 'r3v1' + ) ).first() eq_(result, ({'k1': 'r3v1', 'k2': 'r3v2'},))
\ No newline at end of file |