📋

Description

This error occurs when a JSON numeric value is attempted to be cast to a SQL data type (e.g., INT, DECIMAL) but the value falls outside the acceptable range for that target type. It indicates a data type incompatibility during a `CAST` operation on JSON data.

💬

Error Message

Out of range JSON value for CAST to %s%s from column %s at row %ld

🔍

Known Causes

3 known causes

⚠️

JSON Numeric Value Exceeds Target Type

The numeric value embedded within the JSON data is too large or too small to be stored by the specified target SQL data type (e.g., TINYINT, INT, DATE).

⚠️

Insufficient Precision of Target Type

The target SQL data type for the `CAST` operation does not have enough precision or scale to correctly represent the decimal places or magnitude of the JSON numeric value.

⚠️

Incorrect Target Data Type Used

An inappropriate or too restrictive SQL data type was specified in the `CAST` function, failing to accommodate the expected range of the JSON numbers.

🛠️

Solutions

3 solutions available

1. Identify and Correct Out-of-Range JSON Values medium

Locate the specific JSON value causing the error and adjust it to be within the valid range for the target data type.

1

Examine the error message carefully to identify the column name (`%s%s` before `from column %s`) and the approximate row number (`%ld`).

ERROR MESSAGE: Out of range JSON value for CAST to %s%s from column %s at row %ld

2

Construct a SQL query to fetch the problematic row(s) using the identified column and row number. If the row number is an approximation, you might need to fetch a range of rows around it.

SELECT your_json_column FROM your_table WHERE primary_key_column = <approximate_row_id>;

3

Inspect the JSON content of the fetched row. Look for values that are being cast to a specific numeric type (e.g., `INT`, `BIGINT`, `DECIMAL`) and check if they exceed the maximum or fall below the minimum for that type.

Example: If casting to BIGINT, a value like "1.8446744073709551616e+19" (which is 2^64) would be out of range for a signed BIGINT.

4

Correct the out-of-range value within the JSON data. This might involve: reducing a large number, converting a string representation of a number that's too large, or ensuring the value conforms to the expected data type's limits. You can use `JSON_SET` or `JSON_REPLACE` for this.

UPDATE your_table SET your_json_column = JSON_SET(your_json_column, '$.path.to.value', <corrected_value>) WHERE primary_key_column = <approximate_row_id>;

2. Adjust Target Data Type for CAST Operation medium

If the JSON values are inherently large, modify the target data type in your `CAST` operation to accommodate them.

1

Identify the SQL query or procedure that is performing the `CAST` operation and encountering the error. This is often found in application code or stored procedures.

Example: `SELECT CAST(json_extract(data, '$.some_value') AS SIGNED BIGINT) FROM my_table;`

2

Determine if the target data type in the `CAST` is appropriate for the range of values present in your JSON. For example, if you are consistently seeing values larger than what `BIGINT` can hold, you might need to reconsider the data storage or the cast target.

Consider using `DECIMAL` with sufficient precision and scale for very large numbers if exactness is critical, or a floating-point type like `DOUBLE` if approximation is acceptable.

3

Modify the `CAST` statement in your SQL query or application code to use a data type that can handle the full range of your JSON values. If the JSON values represent very large integers, ensure you are using `SIGNED BIGINT` or consider `DECIMAL`.

Example: If values exceed `BIGINT`, consider a `DECIMAL(65, 0)` if you need exact large integers. `SELECT CAST(json_extract(data, '$.some_value') AS DECIMAL(65, 0)) FROM my_table;`

3. Sanitize JSON Input Before Storage advanced

Implement validation and sanitization logic in your application to ensure JSON data conforms to expected types and ranges before it's inserted or updated in the database.

1

Review the application logic responsible for generating and sending JSON data to the MySQL database.

This could be backend code (e.g., Python, Java, Node.js), API endpoints, or data import scripts.

2

Add validation checks for numerical fields within the JSON. Before sending data to MySQL, verify that numbers fall within acceptable ranges for their intended use.

Example in Python:
python
import json

def validate_and_prepare_json(data):
    if 'large_number_field' in data:
        try:
            num_val = int(data['large_number_field'])
            # Example: Check if within signed BIGINT range
            if not (-9223372036854775808 <= num_val <= 9223372036854775807):
                print(f"Warning: large_number_field {num_val} out of BIGINT range. Clamping or error handling.")
                # Option 1: Clamp the value
                # data['large_number_field'] = max(-9223372036854775808, min(9223372036854775807, num_val))
                # Option 2: Raise an error
                raise ValueError("large_number_field out of BIGINT range")
        except ValueError:
            raise ValueError("large_number_field is not a valid integer")
    return json.dumps(data)

# Usage:
# json_payload = ...
# validated_json_string = validate_and_prepare_json(json_payload)
# INSERT INTO your_table (json_column) VALUES (validated_json_string);

3

If the JSON values are expected to be very large and exceed standard integer types, consider storing them as strings and performing necessary conversions in your application layer when they are needed for calculations, or use `DECIMAL` in MySQL if precision is paramount.

In application logic, if receiving a number like '18446744073709551616', treat it as a string or use a big integer library before attempting to cast it to a fixed-size integer type.

🔗

Related Errors

5 related errors

MySQL Error 3155: JSON Value Out of Range