Fix issues with JSON and float/numeric

Decimal accuracy and behavior has been improved when extracting floating
point and/or decimal values from JSON strings using the
:meth:`_sql.sqltypes.JSON.Comparator.as_float` method, when the numeric
value inside of the JSON string has many significant digits; previously,
MySQL backends would truncate values with many significant digits and SQL
Server backends would raise an exception due to a DECIMAL cast with
insufficient significant digits.   Both backends now use a FLOAT-compatible
approach that does not hardcode significant digits for floating point
values. For precision numerics, a new method
:meth:`_sql.sqltypes.JSON.Comparator.as_numeric` has been added which
accepts arguments for precision and scale, and will return values as Python
``Decimal`` objects with no floating point conversion assuming the DBAPI
supports it (all but pysqlite).

Fixes: #5788
Change-Id: I6eb51fe172a389548dd6e3c65efec9f1f538012e

1 files changed, 19 insertions, 1 deletions

diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 09c7388ab..072afe46e 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -2467,9 +2467,27 @@ class JSON(Indexable, TypeEngine):
             .. versionadded:: 1.3.11
 
             """
-            # note there's no Numeric or Decimal support here yet
             return self._binary_w_type(Float(), "as_float")
 
+        def as_numeric(self, precision, scale, asdecimal=True):
+            """Cast an indexed value as numeric/decimal.
+
+            e.g.::
+
+                stmt = select(
+                    mytable.c.json_column['some_data'].as_numeric(10, 6)
+                ).where(
+                    mytable.c.
+                    json_column['some_data'].as_numeric(10, 6) == 29.75
+                )
+
+            .. versionadded:: 1.4.0b2
+
+            """
+            return self._binary_w_type(
+                Numeric(precision, scale, asdecimal=asdecimal), "as_numeric"
+            )
+
         def as_json(self):
             """Cast an indexed value as JSON.