Fixing Invalid JSON Text Errors in MySQL Inserts and Imports

ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'inv.doc'. is the error everyone meets the first time they insert into a JSON column. The text is better than most databases give you: a reason and a position. But the family of errors has a nastier member, which is not an error at all: the SQL string literal changes your JSON before the JSON parser reads it, and the row goes in with different data. This article decodes the messages, explains the backslash problem with the outputs of each case, and shows how to avoid both by never writing JSON by hand into SQL.

Reading error 3140

The message has three parts: a reason from the parser, a character position counted from zero, and the column. The reasons I produced, with the input that caused each:

{name: "Ada"}                "Missing a name for object member." at position 1
{'name': 'Ada'}              "Missing a name for object member." at position 1
{"a": 1,}                    "Missing a name for object member." at position 8
{"a": 1                      "Missing a comma or '}' after an object member." at position 7
{"a": 01}                    "Missing a comma or '}' after an object member." at position 7
(empty string)               "The document is empty." at position 0
(two spaces)                 "The document is empty." at position 2
undefined                    "Invalid value." at position 0
abc                          "Invalid value." at position 0
{"a": NaN}                   "Invalid value." at position 6
{"a": 1}{"b": 2}             "The document root must not be followed by other values." at position 8
a raw newline in a string    "Invalid encoding in string." at position 11

Read the reason, then jump to the position. "Missing a name for object member" at position 1 is an unquoted or single-quoted key; the same message at a later position after a comma is a trailing comma. "Missing a comma or '}'" means the previous value ended and the next token is unexpected, which includes a number with a leading zero (01 is read as 0 followed by 1) and a truncated document that ends where a comma should be. "Invalid value" is an unrecognized token: undefined, NaN, a bare word. "Invalid encoding in string" is a control character such as a raw newline or tab inside a string, which JSON requires to be escaped. These are the same families as in other parsers; finding the exact line of a JSON syntax error explains how to turn the position into a line and column for long documents.

For an empty string, remember that SQL NULL is a different thing. A nullable column accepts NULL, and rejects ''. An import that writes an empty string for "no value" into a JSON column fails at the first such row, and the fix is nullif(col, '').

The backslash trap

The text between the single quotes in insert ... values ('{...}') is a SQL string literal, and MySQL processes backslash escapes in it before the value reaches any function. The JSON parser sees the result. In the default mode \n is a newline, \" is a double quote, \\ is one backslash, and \U or \a is just U or a. Each of these changes the document:

The first row is the dangerous one. A Windows path pasted into SQL went into the table without an error and with every backslash removed. The unicode escape rows are the same: \u00e9 should become é and became the text u00e9. Nothing fails, and the data is wrong in a way that is found weeks later.

The rule: **to get one backslash into the JSON, write two in SQL, and to get a JSON escape such as \n or \" into the document, write \\n or \\".** That works and is easy to get wrong, and most of the time you should not be hand-writing it.

There are two clean ways out. The first is to build the document with functions, which take values and do the escaping for you:

set @name = 'O\'Brien "Bob" \\ path';
insert into inv (doc) values (json_object('name', @name));
select doc ->> '$.name' from inv order by id desc limit 1;

The stored text was O'Brien "Bob" \ path, with the quotes and the backslash intact and the right escapes in the JSON. The second is to pass the document from the application as a bound parameter (?), which never goes through the SQL literal parser at all. The driver sends the bytes you gave it, and the only parser is the JSON one. That is the right way for application code. It is also how you avoid SQL injection, which hand-built literals invite.

If you must write literals by hand in scripts, NO_BACKSLASH_ESCAPES makes MySQL leave backslashes alone: with the mode on, '{"path": "C:\Users\ada"}' reached the JSON parser unchanged and failed with "Invalid escape character", which is the honest answer, because \U is not a JSON escape. That mode changes the meaning of every string in the session, so use it only in a dedicated import script.

Errors that are not about syntax

Error 3144: Cannot create a JSON value from a string with CHARACTER SET 'binary'. Hex literals (0x7b2261223a317d) and _binary '...' strings have the binary character set, and a JSON value must be text. The fix is convert(x using utf8mb4) or, for a column read from a blob, cast(blob_col as char character set utf8mb4). This is common when a driver sends parameters as binary or when you read from a BLOB column.

Error 3140, "not a JSON text, may need CAST". This is what you get when a value that is not a string is inserted directly into a JSON column: insert into inv (doc) values (1), or true, or now(). In MySQL 8.4 even a multi-value insert with a number failed as a whole. Convert explicitly: cast(1 as json), cast(true as json), json_quote(date_format(now(), '%Y-%m-%d')), or json_object('n', 1). A JSON scalar document is legal, and a string like '"text"' (with its own quotes) or 'true' is accepted as text, but a SQL number is not.

Error 3141: Invalid JSON text in argument 1 to function cast_as_json. cast(@name as json) where @name is O'Brien "Bob" fails because a cast parses its argument as a JSON document. To turn a plain string into a JSON string, quote it first: json_quote(@name) returned "O'Brien \"Bob\" \\ path", and cast(json_quote(@name) as json) stored it. json_unquote is the inverse.

Error 3143: Invalid JSON path expression belongs to path arguments and not to the data; see JSON_EXTRACT and the arrow operators in MySQL.

Imports

For a bulk load, the same problems arrive from files.

• **LOAD DATA INFILE into a JSON column** parses each field as JSON text, and its own escape rules (the ESCAPED BY option) apply to the file before the JSON parser sees the field, much like the SQL literal rules above. I did not run a file load for this article, so test a sample that contains backslashes and quotes before trusting a big import. A load that works on a sample and fails at row 80,000 usually hit a path or a line break in a field.

• A dump from another database might contain JSON as text with Python-style single quotes (str(dict)) or None; those are not JSON, whatever the column says (JSON parse error with single quotes instead of double quotes covers the repair).

• A byte order mark at the start of a file makes the first row fail with "Invalid value" at position 0 (why JSON parse fails on a byte order mark).

• A text column being converted: alter table t modify body json fails on the first bad row and does not name it. Find the rows with select id from t where body is not null and not json_valid(body). In my check json_valid returned 0 for '', for '{'a':1}' with single quotes, and 1 for '1', '"x"' and strings with surrounding spaces.

Load into a staging table with a LONGTEXT column and validate there. It turns "the import died on row 80,000" into "here are the 12 rows that need attention", and from there you can insert the valid rows into the JSON column in one statement. The JSON formatter is a quick way to check one suspicious document by hand.

A checklist for the next error

1. Read the reason and the position. Print the first 60 characters from the position.

2. Check whether the text you see is the text MySQL received: is it a literal in SQL (backslashes!), a bound parameter, or a file?

3. Look for a single quote, a trailing comma, an empty string, undefined, NaN, a BOM, a raw newline.

4. If the data came from code, build it with the language's serializer (json.dumps, JSON.stringify, json_encode) and send it as a parameter.

5. If it came from a file, validate in a staging column first.

When the error is not about the text

The column is not JSON. If a column is TEXT with a CHECK (json_valid(...)), MariaDB style, the error is a constraint violation, not 3140 (MySQL JSON versus MariaDB JSON).

The document is valid and the constraint fails. A CHECK with JSON_SCHEMA_VALID raises error 3819, "Check constraint is violated", which is about shape and not syntax (validating JSON before it gets into a MySQL column).

The value is too large. A document over max_allowed_packet fails with a packet error before it is parsed.

The connection character set is wrong. If the client sends latin1 and the column is utf8mb4, accented text may arrive mangled, and JSON that was valid arrives with invalid byte sequences ("Invalid encoding in string"). Set the connection to utf8mb4.

The error codes are listed in the MySQL server error reference, and the string-literal escape rules in the manual's string literal section.