How to Fix MySQL Error 3140: Invalid JSON Text

ERROR 3140 (22032): Invalid JSON text: "<reason>" at position <N> in value for column '<table>.<column>' is raised when MySQL attempts to parse a string as JSON and the value does not conform to the JSON specification. The error symbol is ER_INVALID_JSON_TEXT.

Impact

Any INSERT, UPDATE, or SET targeting a JSON column will be rejected immediately if the supplied value fails JSON validation. The entire statement rolls back — no partial writes occur. The error message includes the specific parse failure reason and the byte position where parsing stopped, which makes it relatively straightforward to locate the problem in the input.

Developers commonly encounter this when migrating data from a TEXT or VARCHAR column to a JSON column, when application code serializes objects in a non-standard way (single-quoted strings, trailing commas, JavaScript-style unquoted keys), or when an ORM passes a plain string rather than a properly serialized JSON value to a JSON-typed parameter.

Common Causes

Single-quoted JSON strings. MySQL's JSON parser follows the JSON specification strictly, which requires double quotes for strings. Using single quotes (valid in SQL string literals but not in JSON) produces this error.
Unquoted object keys. JavaScript allows {key: "value"}, but JSON requires {"key": "value"}. Code that serializes objects using Object.toString() or similar non-spec methods may produce this format.
Trailing commas. {"a": 1,} and [1, 2, 3,] are not valid JSON, even though many parsers accept them. This often appears when JSON is constructed by hand or via string concatenation.
Plain strings without quotes. Passing a bare word like hello instead of a JSON value ("hello", 42, true, null, [...], {...}) is not valid JSON.
Truncated or partial JSON. Network errors, application bugs, or column size limits on intermediate storage can produce incomplete JSON that is missing closing braces or brackets.
NULL encoded as the string "NULL" or "null" (wrong casing). JSON null (lowercase) is valid; the string "NULL" stored without surrounding double quotes is not. Note that SQL NULL is accepted by a JSON column without error — it is a non-JSON null at the SQL level.
Encoding issues. Multi-byte characters that are misrepresented due to a charset mismatch can introduce invalid byte sequences that break the JSON parser.

Troubleshooting and Resolution Steps

Reproduce the error with the raw value. Use JSON_VALID() to test a candidate string without attempting an insert:

SELECT JSON_VALID('{"key": "value"}');   -- returns 1
SELECT JSON_VALID('{key: "value"}');      -- returns 0

Identify offending rows in bulk data. When migrating or importing rows, find all invalid values before touching the target table:
```
SELECT id, json_col
FROM staging_table
WHERE JSON_VALID(json_col) = 0
LIMIT 100;
```
Fix common quoting issues in SQL. If the data uses single-quoted JSON strings, you cannot simply re-quote them in SQL — fix the serialization at the application layer. For a one-off repair of known-simple values you can use REPLACE():
```
-- Only safe for values where single quotes are exclusively used as JSON string delimiters
UPDATE staging_table
SET json_col = REPLACE(json_col, "'", '"')
WHERE JSON_VALID(json_col) = 0;
```
Audit the result with JSON_VALID() after the update before promoting to the live table.

Use JSON_ARRAY() and JSON_OBJECT() for programmatic construction. Instead of concatenating strings, let MySQL build valid JSON:

SELECT JSON_OBJECT('name', 'Alice', 'age', 30);
-- Result: {"age": 30, "name": "Alice"}

SELECT JSON_ARRAY(1, 'two', true, null);
-- Result: [1, "two", true, null]

Cast a validated string explicitly. If you are certain a string is valid JSON, use CAST(... AS JSON) — it will still raise 3140 on invalid input but makes intent clear:
```
INSERT INTO events (payload)
VALUES (CAST('{"event": "click", "ts": 1700000000}' AS JSON));
```
Check the byte position in the error message. The error text includes the offset where parsing failed, e.g., at position 12. Use that offset to inspect the raw bytes in your application before sending them to MySQL.
Validate in the application layer before inserting. Use the language's built-in JSON parser (e.g., json.loads() in Python, JSON.parse() in JavaScript) to validate and round-trip the value before issuing the SQL statement. Round-tripping (parse → serialize) also normalizes non-standard formats.

Additional Information

Related error: 3141 (ER_INVALID_JSON_TEXT_IN_PARAM). This variant is raised when invalid JSON is passed to a JSON function argument rather than stored in a column. The fix approach is the same.
Related error: 3143 (ER_INVALID_JSON_PATH_CHARSET), 3144 (ER_INVALID_JSON_PATH_WILDCARD). These cover malformed JSON path expressions (e.g., $.foo bar) passed to functions like JSON_EXTRACT() or JSON_SET().
MySQL 5.7+. The native JSON data type and this error code were introduced in MySQL 5.7.8. Prior to that, JSON was typically stored as TEXT with no server-side validation.
MariaDB difference. MariaDB implements its own JSON handling (backed by LONGTEXT internally). Error numbers and messages differ; MariaDB does not necessarily raise 3140 for the same inputs.
ORM behavior. Most ORMs (Hibernate, SQLAlchemy, ActiveRecord, Eloquent) serialize objects to JSON automatically when the column type is mapped to JSON. If you are bypassing the ORM and issuing raw SQL with string interpolation, ensure the value is serialized with the standard library JSON encoder, not str() / toString().
JSON_VALID() returns NULL (not 0) for SQL NULL input. Account for this in WHERE clauses: WHERE JSON_VALID(col) IS NOT TRUE catches both NULL and 0.

Frequently Asked Questions

Why does MySQL reject my JSON even though the same string is accepted by my application's JSON parser? Many application-layer JSON parsers are lenient and accept extensions to the spec (trailing commas, single quotes, comments). MySQL's parser strictly follows RFC 7159 / ECMA-404. Serialize with a strict-mode serializer and the value will be accepted.

Can I store any string in a JSON column if I just wrap it in quotes? A bare string like hello is not valid JSON. The string "hello" (with the double quotes as part of the value) is valid JSON. In SQL you would write the literal as '"hello"' (outer single quotes are the SQL string delimiter; inner double quotes are part of the JSON). Alternatively, use JSON_QUOTE('hello') which handles the escaping for you.

Will this error occur inside a transaction, and will it roll back my other changes? Yes. Error 3140 is a statement-level error. The failing statement is rolled back but, in MySQL's default AUTOCOMMIT mode, earlier statements in the same session that already committed are not affected. If you are inside an explicit transaction, you must handle the error and decide whether to roll back the whole transaction or just skip the offending row.

How do I allow temporarily invalid JSON during a migration? You cannot bypass JSON validation on a JSON-typed column — it is enforced at the storage engine level regardless of SQL mode. The standard approach is to keep the source column as TEXT or VARCHAR, clean the data using JSON_VALID() queries, then alter the column to JSON once all rows are valid.