Error
Error Code:
3143
MySQL Error 3143: Invalid JSON Path Expression
Description
This error indicates that the JSON path expression provided in a MySQL function (e.g., JSON_EXTRACT, JSON_SET, JSON_INSERT) is syntactically incorrect. It occurs when the structure or components of the path do not conform to MySQL's defined JSON path syntax rules, preventing the database from navigating the JSON document.
Error Message
Invalid JSON path expression. The error is around character position %u.%s
Known Causes
4 known causesIncorrect Path Component Syntax
Using an invalid format for keys, array indices, or wildcards within the JSON path expression, such as unquoted keys with special characters or malformed array access.
Misplaced or Missing Quotes
JSON object keys in path expressions often require double quotes. Incorrectly omitting or placing quotes can lead to parsing errors.
Invalid Array Indexing
Attempting to access array elements with non-integer, negative, or otherwise invalid index values in the JSON path.
Incorrect Use of Special Characters
Improperly escaping or handling special characters within path components, causing the parser to misinterpret the path structure.
Solutions
4 solutions available1. Correct JSON Path Syntax easy
Ensure your JSON path expressions adhere to MySQL's supported syntax.
1
Review the MySQL documentation for valid JSON path syntax. Common issues include incorrect use of wildcards, missing quotes around keys with special characters, and improper array indexing.
SELECT JSON_EXTRACT('{"name": "John Doe", "address": {"street": "123 Main St", "city": "Anytown"}}', '$.address.city'); -- Correct
SELECT JSON_EXTRACT('{"name": "John Doe", "address": {"street": "123 Main St", "city": "Anytown"}}', '$.address.zip code'); -- Incorrect (space in key)2
For keys containing spaces or special characters, enclose them in double quotes within the path expression.
SELECT JSON_EXTRACT('{"user data": {"first name": "Jane"}}', '$."user data"."first name"');3
For array elements, use zero-based indexing within square brackets.
SELECT JSON_EXTRACT('{"tags": ["mysql", "json"]}', '$.tags[0]');2. Validate JSON Data Integrity medium
Verify that the JSON data itself is well-formed and not corrupted.
1
Use the `JSON_VALID()` function to check if your JSON data is syntactically correct. This function returns 1 if the JSON is valid, and 0 otherwise.
SELECT JSON_VALID('{"name": "John", "age": 30}'); -- Returns 1 (valid)
SELECT JSON_VALID('{"name": "John", "age": 30'); -- Returns 0 (invalid, missing closing brace)2
If `JSON_VALID()` returns 0, examine the JSON data in your table for syntax errors such as missing commas, unclosed braces or brackets, or incorrect quotation marks. You may need to manually correct the data or re-import it.
UPDATE your_table SET json_column = '{"name": "John", "age": 30}' WHERE id = 1; -- Example correction3. Use `->` Operator for Simpler Paths easy
Leverage the `->` operator for direct column access, which can sometimes simplify path expressions.
1
The `->` operator is a shorthand for `JSON_EXTRACT` when accessing a single value. It can be more readable for straightforward paths.
SELECT json_column -> '$.name' FROM your_table WHERE id = 1; -- Equivalent to JSON_EXTRACT(json_column, '$.name')2
Note that the `->` operator expects a valid JSON path. If the path itself is invalid, you will still encounter error 3143.
SELECT json_column -> '$.user-name' FROM your_table; -- Still requires quoting if 'user-name' has a hyphen and is not handled by the operator implicitly4. Inspect the Error Position and Context medium
Use the error message's character position to pinpoint the exact location of the syntax error.
1
The error message 'The error is around character position %u.%s' provides a clue. The `%u` is the character offset, and `%s` is the problematic token. Carefully examine the JSON path expression at that specific character position.
Example error: 'Invalid JSON path expression. The error is around character position 7.address.city'
This suggests the error is near the 7th character, potentially related to 'address' or the dot before it.2
Manually count characters in your JSON path expression, starting from 1 for the first character. The position indicates where the parser encountered an unexpected token or pattern.
Consider the path: '$.user.address.street'. If the error is at position 15, it's likely within 'address.street'.