MySQL raises ER_NUMERIC_JSON_VALUE_OUT_OF_RANGE when a numeric value stored in JSON is cast into a SQL column or expression that cannot represent the number's magnitude or scale.
ER_NUMERIC_JSON_VALUE_OUT_OF_RANGE appears when MySQL casts a JSON number that exceeds the target data type's range. Use a larger numeric type, adjust the value, or CAST into DECIMAL/DOUBLE to resolve the issue.
ER_NUMERIC_JSON_VALUE_OUT_OF_RANGE
The error fires when MySQL converts a numeric literal from a JSON document into a SQL type that is too small to store it. The server stops the statement and returns SQLSTATE 22003, indicating numeric value out of range.
Introduced in MySQL 5.7.8, the check protects tables from silent truncation. It often appears in INSERT, UPDATE, or SELECT ... JSON_TABLE operations that perform an implicit or explicit CAST.
The most common trigger is extracting a large JSON number with ->> or JSON_TABLE and letting MySQL cast it to INT, BIGINT, or DECIMAL with insufficient precision. Implicit conversion during comparisons or arithmetic can also fail.
The problem surfaces in bulk loads, ETL jobs, and API ingest pipelines where numeric sizes vary and schema constraints are tight.
First, confirm which column or CAST expression overflowed by reading the full error text. Then enlarge the destination data type or reduce the JSON number before casting. Using CAST(... AS DECIMAL(65,30)) often resolves the overflow safely.
If schema changes are impossible, wrap the extraction in JSON_EXTRACT and validate the value in application code before inserting.
During INSERT ... SELECT, developers frequently map JSON values to INT columns. Switch those columns to BIGINT or UNSIGNED BIGINT if the values exceed 2^31-1.
When creating a generated column from JSON, declare it DECIMAL with enough precision. MySQL evaluates generated columns row by row, so an oversized JSON number will abort the entire statement.
Always profile incoming JSON with JSON_SCHEMA_VALIDATION or a staging table. Define numeric columns with headroom and add CHECK constraints to reject impossible magnitudes early.
In Galaxy's SQL editor, you can preview JSON extraction results and adjust CAST statements interactively, reducing trial-and-error and preventing production failures.
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD occurs when a string cannot be converted to the target data type, while ER_BAD_FIELD_ERROR appears when a JSON path references a missing key. Tackle them with similar schema reviews and explicit casting strategies.
Implicit conversion of a large JSON number to INT or BIGINT.
Explicit CAST of JSON value to DECIMAL with too few digits.
Generated columns reading oversized numeric tokens from JSON.
JSON_TABLE columns defined with SMALLINT, MEDIUMINT or TINYINT.
Occurs when MySQL cannot convert a string or numeric literal to the designated column type. Review data cleanliness and column definitions.
Triggers when inserting a value that exceeds the allowed range for signed or unsigned integers. Expand the data type to fix.
Appears when the input string is not valid JSON. Validate JSON before parsing.
Yes. If SELECT involves JSON extraction that implicitly casts to a narrow type, the statement will fail.
No. MySQL enforces it to prevent silent data loss. You must adjust data types or values.
Early rows may fit the target range, but a larger value in a subsequent row triggers the error. Always test with worst-case data.
Galaxy highlights JSON extraction results inline, letting you preview ranges and tweak CASTs before running costly migrations.
.avif)
.avif){kind=logo}
