How to Fix Invalid JSON Text Errors in MySQL

MySQL throws an “Invalid JSON text” error (most commonly Error Code 3140) whenever an operation or function receives a text string that violates the strict syntax rules of RFC 8259. To resolve it, you must filter out or correct malformed entries using validation functions before attempting extraction, or ensure incoming strings conform to valid document structures before inserting them into native JSON columns.

What Does Invalid JSON Mean in MySQL?

MySQL adheres to the RFC 8259 standard to manage and evaluate JSON data. According to this specification, a valid JSON document must consist of an object, an array, or a valid scalar value such as a string, number, boolean, or literal null. Under RFC 8259, a blank value or empty string is not a valid JSON document, even though developers often mistake an empty string for empty data.

When MySQL reports “Invalid JSON text”, the server has encountered a byte or character sequence that its internal parser cannot decode into a valid document structure. This error typically surfaces in one of several variations:

• ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position X
• Invalid JSON text: "The document is empty." at position 0
• Error Code: 3140. Invalid JSON text: "Invalid encoding in string." at position X

For a document to be valid, all supported characters within strings must adhere to proper JSON formatting, including balanced quotation marks, correctly escaped control characters, and valid structural delimiters like curly braces and square brackets. If a closing bracket is mismatched, a character encoding is corrupted, or a text sequence ends prematurely, the parser halts at the offending byte offset and reports the error.

How Invalid JSON Enters MySQL and Breaks Queries

MySQL provides a native JSON data type that automatically validates documents upon insertion or update. If you attempt to write invalid text directly into a column defined with the native JSON type, the database engine rejects the write and produces an error. However, invalid JSON frequently exists inside databases for three primary reasons:

1. Storage in Standard Text Columns: Applications often store JSON payloads inside VARCHAR, TEXT, MEDIUMTEXT, or LONGTEXT columns. Because standard string columns do not enforce automatic validation, broken payloads, plain strings, or empty strings are accepted without complaint.
2. Validation Bypasses: Using statements like INSERT IGNORE or running database sessions with disabled strict SQL mode can bypass standard validation checks on native JSON columns. In these scenarios, MySQL may force insertion by coercing unassigned or invalid inputs into zero-values like an empty string, creating an invalid state inside the underlying storage.
3. Payload Truncation and Packet Limits: The size of any JSON document stored in a JSON column is bound by the max_allowed_packet system variable. If an application attempts to write large payloads—such as large base64-encoded file content—that exceed size constraints or experience network truncation, the resulting fragment cuts off mid-string. This generates syntax errors such as “Invalid encoding in string” or “The document is empty.”

The breakdown happens downstream: when an application later calls a JSON function like JSON_EXTRACT() on a non-validated string column, the function expects a fully formed document. The moment JSON_EXTRACT() processes a row containing invalid syntax, the entire query fails.

Step-by-Step Guide to Finding and Fixing Malformed JSON

To demonstrate how to locate and repair malformed data, consider an audit table named device_telemetry where raw event payloads are stored in a VARCHAR column named payload_text:

CREATE TABLE device_telemetry (
 telemetry_id INT PRIMARY KEY,
 device_code VARCHAR(32),
 payload_text VARCHAR(255)
);

INSERT INTO device_telemetry (telemetry_id, device_code, payload_text) VALUES
(1, 'DEV-A', '{"status": "active", "battery_level": 94}'),
(2, 'DEV-B', NULL),
(3, 'DEV-C', ''),
(4, 'DEV-D', '{"status": "rebooting", "battery_level": 12]'),
(5, 'DEV-E', '{"status": "idle", "battery_level": 78}');

If you run SELECT JSON_EXTRACT(payload_text, '$.battery_level') FROM device_telemetry;, MySQL will immediately abort the query because row 3 contains an empty string and row 4 contains an invalid closing bracket.

Step 1: Identify Rows with Invalid JSON Text

MySQL provides the JSON_VALID() function to test whether a value represents valid JSON. JSON_VALID() returns 1 if the string complies with RFC 8259 syntax, 0 if it is invalid, and NULL if the input is NULL.

Execute the following query to isolate all rows with invalid text:

SELECT telemetry_id, device_code, payload_text
FROM device_telemetry
WHERE NOT JSON_VALID(payload_text)
 OR payload_text IS NULL;

This query targets the broken records:

• Row 2 (NULL)
• Row 3 (empty string, which evaluates to 0)
• Row 4 (mismatched delimiter ], which evaluates to 0)

Step 2: Safely Extract Values from Clean Rows

If you need immediate read access to the operational data while repairs are pending, you can protect your extraction queries by combining JSON_VALID() with JSON_EXTRACT() in your WHERE clause:

SELECT 
 telemetry_id,
 device_code,
 JSON_EXTRACT(payload_text, '$.battery_level') AS battery_level
FROM device_telemetry
WHERE JSON_VALID(payload_text)
 AND JSON_EXTRACT(payload_text, '$.battery_level') IS NOT NULL;

This statement extracts the target field from rows 1 and 5, skipping rows 2, 3, and 4 entirely without triggering parser errors.

Step 3: Inspect and Correct the Invalid Payloads

Next, examine each invalid string identified in Step 1. For complex nested structures where bracket mismatches or character escaping errors are difficult to spot manually, paste the raw string into JSON Formatter to quickly reveal syntax errors and identify missing delimiters.

Once the proper structure is determined, update the records with valid RFC 8259 documents:

-- Correct the empty string to a valid empty object
UPDATE device_telemetry
SET payload_text = '{}'
WHERE telemetry_id = 3;

-- Fix the mismatched closing bracket in row 4
UPDATE device_telemetry
SET payload_text = '{"status": "rebooting", "battery_level": 12}'
WHERE telemetry_id = 4;

After running these updates, SELECT * FROM device_telemetry WHERE NOT JSON_VALID(payload_text); will return zero rows.

Troubleshooting Complex Cases and Native Column Constraints

If your setup does not fit a simple text-column repair scenario, review the following conditions:

Insertion Failures on Native JSON Columns

When an INSERT or UPDATE fails with ERROR 3140, your application is attempting to push an invalid literal or empty string directly into a column defined as JSON. Native JSON columns automatically validate inputs. Ensure your application serializes null states as the SQL NULL keyword or the string 'null', empty objects as '{}', and empty arrays as '[]', rather than passing empty strings ('').

Truncation and Large Payload Encoding Errors

If you see errors like Invalid encoding in string or The document is empty at positions corresponding to multi-megabyte payloads, verify your database configuration:

• Inspect the max_allowed_packet system variable. If a JSON document exceeds this limit during storage, the server truncates the payload, causing syntax breakdown.
• Check whether your storage column is restricted by an undersized data type (such as standard TEXT or VARCHAR), which clips data before insertion. When storing large JSON blobs, ensure the underlying type accommodates the full length.

Migrating String Columns to Native JSON

To prevent malformed text from recurring, migrate legacy string columns to the native JSON type. Native columns store documents in an optimized binary format rather than raw text. This format permits quick read access to document elements without reparsing and prevents any future insertion of invalid syntax:

-- 1. Verify all existing data is valid
SELECT COUNT(*)
FROM device_telemetry
WHERE payload_text IS NOT NULL 
 AND NOT JSON_VALID(payload_text);

-- 2. Alter column to native JSON once count is 0
ALTER TABLE device_telemetry 
MODIFY COLUMN payload_text JSON;