diff options
Diffstat (limited to 'lib/sqlalchemy')
| -rw-r--r-- | lib/sqlalchemy/sql/sqltypes.py | 50 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/type_api.py | 3 | ||||
| -rw-r--r-- | lib/sqlalchemy/testing/suite/test_types.py | 59 |
3 files changed, 105 insertions, 7 deletions
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index ef1624fa0..215c09e60 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -1728,7 +1728,55 @@ class JSON(Indexable, TypeEngine): Index operations return an expression object whose type defaults to :class:`.JSON` by default, so that further JSON-oriented instructions - may be called upon the result type. + may be called upon the result type. Note that there are backend-specific + idiosyncracies here, including that the Postgresql database does not generally + compare a "json" to a "json" structure without type casts. These idiosyncracies + can be accommodated in a backend-neutral way by by making explicit use + of the :func:`.cast` and :func:`.type_coerce` constructs. + Comparison of specific index elements of a :class:`.JSON` object + to other objects work best if the **left hand side is CAST to a string** + and the **right hand side is rendered as a json string**; a future SQLAlchemy + feature such as a generic "astext" modifier may simplify this at some point: + + * **Compare an element of a JSON structure to a string**:: + + from sqlalchemy import cast, type_coerce + from sqlalchemy import String, JSON + + cast( + data_table.c.data['some_key'], String + ) == '"some_value"' + + cast( + data_table.c.data['some_key'], String + ) == type_coerce("some_value", JSON) + + * **Compare an element of a JSON structure to an integer**:: + + from sqlalchemy import cast, type_coerce + from sqlalchemy import String, JSON + + cast(data_table.c.data['some_key'], String) == '55' + + cast( + data_table.c.data['some_key'], String + ) == type_coerce(55, JSON) + + * **Compare an element of a JSON structure to some other JSON structure** - note + that Python dictionaries are typically not ordered so care should be taken + here to assert that the JSON structures are identical:: + + from sqlalchemy import cast, type_coerce + from sqlalchemy import String, JSON + import json + + cast( + data_table.c.data['some_key'], String + ) == json.dumps({"foo": "bar"}) + + cast( + data_table.c.data['some_key'], String + ) == type_coerce({"foo": "bar"}, JSON) 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 diff --git a/lib/sqlalchemy/sql/type_api.py b/lib/sqlalchemy/sql/type_api.py index 98ede4e66..bb9de20fc 100644 --- a/lib/sqlalchemy/sql/type_api.py +++ b/lib/sqlalchemy/sql/type_api.py @@ -1213,6 +1213,9 @@ class Variant(TypeDecorator): self.impl = base self.mapping = mapping + def coerce_compared_value(self, operator, value): + return self.impl.coerce_compared_value(operator, value) + def load_dialect_impl(self, dialect): if dialect.name in self.mapping: return self.mapping[dialect.name] diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py index d85531396..dbbe03111 100644 --- a/lib/sqlalchemy/testing/suite/test_types.py +++ b/lib/sqlalchemy/testing/suite/test_types.py @@ -5,7 +5,7 @@ from ..assertions import eq_ from ..config import requirements from sqlalchemy import Integer, Unicode, UnicodeText, select from sqlalchemy import Date, DateTime, Time, MetaData, String, \ - Text, Numeric, Float, literal, Boolean, cast, null, JSON, and_ + Text, Numeric, Float, literal, Boolean, cast, null, JSON, and_, type_coerce from ..schema import Table, Column from ... import testing import decimal @@ -623,6 +623,12 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): } } + data6 = { + "a": 5, + "b": "some value", + "c": {"foo": "bar"} + } + @classmethod def define_tables(cls, metadata): Table('data_table', metadata, @@ -730,10 +736,11 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): {"name": "r2", "data": self.data2}, {"name": "r3", "data": self.data3}, {"name": "r4", "data": self.data4}, - {"name": "r5", "data": self.data5}] + {"name": "r5", "data": self.data5}, + {"name": "r6", "data": self.data6}] ) - def _test_index_criteria(self, crit, expected): + def _test_index_criteria(self, crit, expected, test_literal=True): self._criteria_fixture() with config.db.connect() as conn: stmt = select([self.tables.data_table.c.name]).where(crit) @@ -743,10 +750,11 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): expected ) - literal_sql = str(stmt.compile( - config.db, compile_kwargs={"literal_binds": True})) + if test_literal: + literal_sql = str(stmt.compile( + config.db, compile_kwargs={"literal_binds": True})) - eq_(conn.scalar(literal_sql), expected) + eq_(conn.scalar(literal_sql), expected) def test_crit_spaces_in_key(self): name = self.tables.data_table.c.name @@ -791,6 +799,45 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): "r5" ) + def test_crit_against_string_basic(self): + name = self.tables.data_table.c.name + col = self.tables.data_table.c['data'] + + self._test_index_criteria( + and_(name == 'r6', cast(col["b"], String) == '"some value"'), + "r6" + ) + + def test_crit_against_string_coerce_type(self): + name = self.tables.data_table.c.name + col = self.tables.data_table.c['data'] + + self._test_index_criteria( + and_(name == 'r6', + cast(col["b"], String) == type_coerce("some value", JSON)), + "r6", + test_literal=False + ) + + def test_crit_against_int_basic(self): + name = self.tables.data_table.c.name + col = self.tables.data_table.c['data'] + + self._test_index_criteria( + and_(name == 'r6', cast(col["a"], String) == '5'), + "r6" + ) + + def test_crit_against_int_coerce_type(self): + name = self.tables.data_table.c.name + col = self.tables.data_table.c['data'] + + self._test_index_criteria( + and_(name == 'r6', cast(col["a"], String) == type_coerce(5, JSON)), + "r6", + test_literal=False + ) + def test_unicode_round_trip(self): s = select([ cast( |