📋

Description

This error indicates that MySQL encountered a string that it cannot parse as valid JSON. It typically occurs when attempting to insert or update data in a JSON column with malformed JSON, or when using JSON functions on non-JSON strings. The error message pinpoints the exact position of the invalid character.

💬

Error Message

Invalid JSON text: %s at position %u in value for column '%s'.

🔍

Known Causes

3 known causes

⚠️

Malformed JSON Syntax

The input string contains syntax errors, such as missing quotes, commas, brackets, or incorrect data types, preventing MySQL from parsing it as valid JSON.

⚠️

Incorrect Data Type for Column

An attempt was made to insert non-JSON formatted data into a column explicitly defined with the `JSON` data type.

⚠️

Character Encoding Mismatch

Special characters or non-ASCII characters in the JSON string are not correctly encoded or decoded, leading to parsing failures.

🛠️

Solutions

4 solutions available

1. Validate and Correct JSON Data Before Insertion easy

Ensure the JSON string being inserted or updated is valid before it reaches the database.

1

Identify the source of the invalid JSON. This could be application code, an ETL process, or direct user input.

2

Use a JSON validator in your application code or an external tool to check the integrity of the JSON string. For example, in Python:

import json

try:
    json.loads(your_json_string)
    print('JSON is valid')
except json.JSONDecodeError as e:
    print(f'JSON is invalid: {e}')

3

If the JSON is invalid, correct the formatting. Common issues include missing commas, unquoted keys or values, or incorrect bracket usage. Then, re-attempt the insertion or update.

2. Use MySQL's JSON Functions for Data Manipulation medium

Leverage MySQL's built-in JSON functions to safely construct and modify JSON data.

1

Instead of directly inserting a raw JSON string, use MySQL's `JSON_OBJECT` and `JSON_ARRAY` functions to build your JSON structure. This ensures valid JSON is created.

UPDATE your_table
SET your_json_column = JSON_OBJECT('key1', 'value1', 'key2', 123, 'key3', JSON_ARRAY('item1', 'item2'))
WHERE id = your_id;

2

When updating existing JSON data, use functions like `JSON_SET`, `JSON_INSERT`, or `JSON_REPLACE` to modify specific parts of the JSON document. These functions handle JSON structure validation internally.

UPDATE your_table
SET your_json_column = JSON_SET(your_json_column, '$.key1', 'new_value')
WHERE id = your_id;

3

If you need to parse and then re-serialize JSON, use `JSON_EXTRACT` and then `JSON_OBJECT`/`JSON_ARRAY` or string concatenation carefully.

UPDATE your_table
SET your_json_column = JSON_OBJECT(
    'existing_key', JSON_UNQUOTE(JSON_EXTRACT(your_json_column, '$.existing_key')),
    'new_key', 'new_value'
)
WHERE id = your_id;

3. Clean and Repair Existing Invalid JSON Data advanced

Address existing invalid JSON in your table by cleaning and updating the affected rows.

1

Identify rows with invalid JSON. You can do this by attempting to use JSON functions on the column and looking for errors, or by using a more direct string search for common JSON syntax errors.

SELECT * FROM your_table WHERE JSON_VALID(your_json_column) = 0;

2

For each identified row, manually inspect the `your_json_column` value. You might need to retrieve the data, fix it using a JSON editor or script, and then update the row.

SELECT your_json_column FROM your_table WHERE id = <row_id>;

3

Once you have the corrected JSON string, update the row.

UPDATE your_table
SET your_json_column = '{"corrected": "json_string"}'
WHERE id = <row_id>;

4

For bulk repairs, consider writing a script that fetches rows with invalid JSON, attempts to parse and clean them (e.g., by removing trailing commas, fixing quotes), and then updates the table. This requires careful error handling.

4. Ensure Correct Data Type for JSON Column easy

Verify that the column intended for JSON data is actually defined as a JSON data type.

1

Check the table schema to confirm the data type of the column you are using for JSON. It should be `JSON`.

SHOW CREATE TABLE your_table;

2

If the column is currently a `VARCHAR`, `TEXT`, or similar string type, and you intend to store JSON, you should alter the table to use the `JSON` data type. **Caution:** This will attempt to validate existing data. Back up your data first.

ALTER TABLE your_table MODIFY COLUMN your_json_column JSON;

3

If the `ALTER TABLE` statement fails due to invalid existing data, you'll need to clean the data first using Solution 3 before altering the column type.

🔗

Related Errors

5 related errors

MySQL Error 3140: Invalid JSON Text Encountered