diff options
Diffstat (limited to 'lib/sqlalchemy/sql')
| -rw-r--r-- | lib/sqlalchemy/sql/sqltypes.py | 198 |
1 files changed, 154 insertions, 44 deletions
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index 7829eb4d0..751b0d0ae 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -1961,7 +1961,9 @@ class JSON(Indexable, TypeEngine): data = {"key1": "value1", "key2": "value2"} ) - The base :class:`.types.JSON` provides these operations: + **JSON-Specific Expression Operators** + + The :class:`.types.JSON` datatype provides these additional SQL operations: * Keyed index operations:: @@ -1975,63 +1977,70 @@ class JSON(Indexable, TypeEngine): data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')] - Additional operations are available from the dialect-specific versions + * Data casters for specific JSON element types, subsequent to an index + or path operation being invoked:: + + data_table.c.data["some key"].as_integer() + + .. versionadded:: 1.3.11 + + Additional operations may be available from the dialect-specific versions of :class:`.types.JSON`, such as :class:`.postgresql.JSON` and - :class:`.postgresql.JSONB`, each of which offer more operators than - just the basic type. + :class:`.postgresql.JSONB` which both offer additional PostgreSQL-specific + operations. + + **Casting JSON Elements to Other Types** + + Index operations, i.e. those invoked by calling upon the expression using + the Python bracket operator as in ``some_column['some key']``, 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. + However, it is likely more common that an index operation is expected + to return a specific scalar element, such as a string or integer. In + order to provide access to these elements in a backend-agnostic way, + a series of data casters are provided: - 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. Note that there are backend-specific - idiosyncrasies here, including that the PostgreSQL database does not - generally compare a "json" to a "json" structure without type casts. These - idiosyncrasies can be accommodated in a backend-neutral way 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 works 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: + * :meth:`.JSON.Comparator.as_string` - return the element as a string - * **Compare an element of a JSON structure to a string**:: + * :meth:`.JSON.Comparator.as_boolean` - return the element as a boolean - from sqlalchemy import cast, type_coerce - from sqlalchemy import String, JSON + * :meth:`.JSON.Comparator.as_float` - return the element as a float - cast( - data_table.c.data['some_key'], String - ) == '"some_value"' + * :meth:`.JSON.Comparator.as_integer` - return the element as an integer - cast( - data_table.c.data['some_key'], String - ) == type_coerce("some_value", JSON) + These data casters are implemented by supporting dialects in order to + assure that comparisons to the above types will work as expected, such as:: - * **Compare an element of a JSON structure to an integer**:: + # integer comparison + data_table.c.data["some_integer_key"].as_integer() == 5 - from sqlalchemy import cast, type_coerce - from sqlalchemy import String, JSON + # boolean comparison + data_table.c.data["some_boolean"].as_boolean() == True - cast(data_table.c.data['some_key'], String) == '55' + .. versionadded:: 1.3.11 Added type-specific casters for the basic JSON + data element types. - cast( - data_table.c.data['some_key'], String - ) == type_coerce(55, JSON) + .. note:: + + The data caster functions are new in version 1.3.11, and supersede + the previous documented approaches of using CAST; for reference, + this looked like:: + + from sqlalchemy import cast, type_coerce + from sqlalchemy import String, JSON + 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:: + The above case now works directly as:: - from sqlalchemy import cast, type_coerce - from sqlalchemy import String, JSON - import json + data_table.c.data['some_key'].as_integer() == 5 - cast( - data_table.c.data['some_key'], String - ) == json.dumps({"foo": "bar"}) + For details on the previous comparison approach within the 1.3.x + series, see the documentation for SQLAlchemy 1.2 or the included HTML + files in the doc/ directory of the version's distribution. - cast( - data_table.c.data['some_key'], String - ) == type_coerce({"foo": "bar"}, JSON) + **Detecting Changes in JSON columns when using the ORM** 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 @@ -2040,6 +2049,8 @@ class JSON(Indexable, TypeEngine): will be detected by the unit of work. See the example at :class:`.HSTORE` for a simple example involving a dictionary. + **Support for JSON null vs. SQL NULL** + When working with NULL values, the :class:`.JSON` type recommends the use of two specific constants in order to differentiate between a column that evaluates to SQL NULL, e.g. no value, vs. the JSON-encoded string @@ -2064,6 +2075,8 @@ class JSON(Indexable, TypeEngine): values, but care must be taken as to the value of the :paramref:`.JSON.none_as_null` in these cases. + **Customizing the JSON Serializer** + The JSON serializer and deserializer used by :class:`.JSON` defaults to Python's ``json.dumps`` and ``json.loads`` functions; in the case of the psycopg2 dialect, psycopg2 may be using its own custom loader function. @@ -2092,6 +2105,8 @@ class JSON(Indexable, TypeEngine): :class:`.mysql.JSON` + :class:`.sqlite.JSON` + .. versionadded:: 1.1 @@ -2249,6 +2264,101 @@ class JSON(Indexable, TypeEngine): return operator, index, self.type + def as_boolean(self): + """Cast an indexed value as boolean. + + e.g.:: + + stmt = select([ + mytable.c.json_column['some_data'].as_boolean() + ]).where( + mytable.c.json_column['some_data'].as_boolean() == True + ) + + .. versionadded:: 1.3.11 + + """ + return self._binary_w_type(Boolean(), "as_boolean") + + def as_string(self): + """Cast an indexed value as string. + + e.g.:: + + stmt = select([ + mytable.c.json_column['some_data'].as_string() + ]).where( + mytable.c.json_column['some_data'].as_string() == + 'some string' + ) + + .. versionadded:: 1.3.11 + + """ + return self._binary_w_type(String(), "as_string") + + def as_integer(self): + """Cast an indexed value as integer. + + e.g.:: + + stmt = select([ + mytable.c.json_column['some_data'].as_integer() + ]).where( + mytable.c.json_column['some_data'].as_integer() == 5 + ) + + .. versionadded:: 1.3.11 + + """ + return self._binary_w_type(Integer(), "as_integer") + + def as_float(self): + """Cast an indexed value as float. + + e.g.:: + + stmt = select([ + mytable.c.json_column['some_data'].as_float() + ]).where( + mytable.c.json_column['some_data'].as_float() == 29.75 + ) + + .. versionadded:: 1.3.11 + + """ + # note there's no Numeric or Decimal support here yet + return self._binary_w_type(Float(), "as_float") + + def as_json(self): + """Cast an indexed value as JSON. + + This is the default behavior of indexed elements in any case. + + Note that comparison of full JSON structures may not be + supported by all backends. + + .. versionadded:: 1.3.11 + + """ + return self.expr + + def _binary_w_type(self, typ, method_name): + if not isinstance( + self.expr, elements.BinaryExpression + ) or self.expr.operator not in ( + operators.json_getitem_op, + operators.json_path_getitem_op, + ): + raise exc.InvalidRequestError( + "The JSON cast operator JSON.%s() only works with a JSON " + "index expression e.g. col['q'].%s()" + % (method_name, method_name) + ) + expr = self.expr._clone() + expr.type = typ + return expr + comparator_factory = Comparator @property |