I ran into this case using MySQL 5.6:
This query works and returns expected results:
select *
from some_table
where a = 'b'
and metadata->>"$.country" is not null;
However, this query (the only difference is the order of where clauses) returns an error
select *
from some_table
where metadata->>"$.country" is not null
and a = 'b';
The error MySQL returns is
Invalid JSON text in argument 1 to function json_extract: "Invalid value." at position 0.
Why?
The value of the metdata column contains malformed JSON for at least one row in the table.
We'd expect that entirely removing the a = 'b' condition, we would also observe the same error.
I suspect that the difference in behavior is due to the order of operations being performed. When the a = 'b' condition is evaluated first, that excludes rows before the JSON_EXTRACT(metadata) expression is evaluated. Since the row doesn't match the a = 'b' condition, MySQL takes a shortcut, it doesn't evaluate the JSON_EXTRACT, it already knows the row is going to be excluded.
When the comparisons are done in a different order, with the JSON_EXTRACT function executed first, the error is raised when the expression is evaluated for a rows with invalid JSON in metadata.
Summary:
There's at least one row in the table that has malformed JSON stored in metadata column.
The difference in the observed behavior of the two queries is due to a different order of operations.
Suggestion:
Consider using the JSON_VALID function to identify rows with invalid values.
Excerpt from MySQL Reference Manual
JSON_EXTRACT
Returns data from a JSON document, selected from the parts of the document matched by the path arguments. Returns NULL if any argument is NULL or no paths locate a value in the document. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression.
https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-extract
JSON_VALID
https://dev.mysql.com/doc/refman/5.7/en/json-attribute-functions.html#function_json-valid