📋

Description

This error indicates that a value extracted from a JSON document, intended for a specific column in a `JSON_TABLE` function, exceeds the permissible range or capacity for that column's data type. It typically occurs when the `JSON_TABLE` function attempts to convert a JSON value (e.g., a number, string, or boolean) into a SQL column type (e.g., `INT`, `DECIMAL`, `DATE`) but the value is too large, too small, or incorrectly formatted for the target type.

💬

Error Message

Value is out of range for JSON_TABLE's column '%s'

🔍

Known Causes

4 known causes

⚠️

Data Type Mismatch or Overflow

A numeric value extracted from JSON is too large or too small to fit into the target SQL column's data type (e.g., trying to store a large number in a SMALLINT column).

⚠️

Invalid Date/Time Format

A string value from JSON is being converted to a `DATE`, `TIME`, or `DATETIME` column, but its format does not match MySQL's expected format for that type.

⚠️

Out-of-Range Enum/Set Value

The JSON value being inserted into an `ENUM` or `SET` column is not one of the predefined values allowed for that column type.

⚠️

Incorrect Precision/Scale

A numeric value from JSON exceeds the defined precision or scale for a `DECIMAL` or `NUMERIC` target column.

🛠️

Solutions

3 solutions available

1. Adjust Target Column Data Type and Size easy

Modify the data type or size of the target column in your `JSON_TABLE` to accommodate the JSON value.

1

Identify the specific column in your `JSON_TABLE` definition that is causing the 'out of range' error. The error message will typically indicate the column name (e.g., '%s').

SELECT ... FROM JSON_TABLE(json_doc, 'path' COLUMNS (your_problem_column VARCHAR(50) ...)) AS jt;

2

Examine the JSON data you are processing. Determine the maximum expected length or value for the data that will be mapped to `your_problem_column`.

SELECT your_json_column FROM your_table WHERE ...;

3

Alter the `JSON_TABLE` definition to use a data type and size that can hold the maximum value. For strings, increase the length of `VARCHAR` or use `TEXT`/`LONGTEXT`. For numeric types, ensure the range of the chosen type (e.g., `INT`, `BIGINT`, `DECIMAL`) is sufficient.

ALTER TABLE your_table_containing_json MODIFY COLUMN your_json_column LONGTEXT; -- If the issue is with reading the JSON itself

-- Example of adjusting JSON_TABLE column definition:
SELECT jt.your_problem_column FROM your_table, 
  JSON_TABLE(your_table.json_data, '$.some_array[*]' COLUMNS (
    your_problem_column VARCHAR(255) PATH '$.some_field'
  )) AS jt;

4

Re-run your query that uses `JSON_TABLE`. If the data type or size was the issue, the error should be resolved.

2. Truncate or Sanitize JSON Data Before Processing medium

Cleanse the incoming JSON data to ensure values fit within the defined column constraints.

1

Identify the source of the JSON data and the specific field causing the 'out of range' error. This might involve inspecting the raw JSON or the application logic that generates it.

2

Implement logic to truncate or sanitize the problematic JSON values before they are inserted into your database or processed by `JSON_TABLE`. This can be done in your application code or using database triggers.

UPDATE your_table SET json_data = JSON_SET(json_data, '$.some_array[*].some_field', SUBSTRING(JSON_EXTRACT(json_data, '$.some_array[*].some_field'), 1, 255)) WHERE LENGTH(JSON_EXTRACT(json_data, '$.some_array[*].some_field')) > 255; -- Example for VARCHAR(255) limit

3

Alternatively, if you have control over the JSON generation, modify the source to produce values that conform to your database schema's constraints.

4

After sanitizing the data, re-run your `JSON_TABLE` query. The error should be resolved as the values now fit within the column definitions.

3. Review and Correct JSON Path Expressions medium

Ensure your JSON path expressions correctly target the intended data, which might be unintentionally selecting overly large values.

1

Carefully examine the JSON path expressions used in your `JSON_TABLE` definition. Verify that they are pointing to the correct fields and that you are not inadvertently selecting a large aggregated value or an entire nested object when you intend to select a specific scalar value.

SELECT jt.value FROM your_table, 
  JSON_TABLE(your_table.json_data, '$.some_array[*]' COLUMNS (
    value VARCHAR(50) PATH '$.some_field.nested_field' -- Is this path correct?
  )) AS jt;

2

Test your JSON path expressions independently using `JSON_EXTRACT` or by using a JSON validator tool to understand what data they are retrieving.

SELECT JSON_EXTRACT(your_table.json_data, '$.some_array[*].some_field.nested_field') FROM your_table WHERE ...;

3

Adjust the JSON path expressions to precisely target the scalar values you intend to extract. If the path is selecting a larger structure than expected, refine it to be more specific.

SELECT jt.value FROM your_table, 
  JSON_TABLE(your_table.json_data, '$.some_array[*]' COLUMNS (
    value VARCHAR(50) PATH '$.some_field.nested_field.specific_value' -- More specific path
  )) AS jt;

4

Re-execute your `JSON_TABLE` query with the corrected path expressions. This should resolve the 'out of range' error if the path was the root cause.

🔗

Related Errors

5 related errors

MySQL Error 3669: JSON_TABLE Column Value Out of Range