Why does is JSON_EXTRACT's return value treated differently depending on the operator?

Question

I'm using MySQL 8.0 (inside Azure Database for MySQL Server).

I've noticed some strange behavior while querying with the "json_extract" functionality. In the example below, I am querying a VARCHAR field that contains the valid JSON text: {"myfield":"123456"}.

SELECT json_extract(jsontext, '$.myfield') raw_extract,
       LENGTH(json_extract(jsontext, '$.myfield')) raw_extract_length,
       CASE WHEN json_extract(jsontext, '$.myfield') = '123456' THEN 'true' ELSE 'false' END matches_no_quotes,
       CASE WHEN json_extract(jsontext, '$.myfield') = '"123456"' THEN 'true' ELSE 'false' END matches_with_quotes,
       CASE WHEN json_extract(jsontext, '$.myfield') LIKE '"%"' THEN 'true' ELSE 'false' END matches_wildcard_dbl_quotes

What is odd is that the value returned by json_extract appear to behave differently depending on what comparison is interacting with it:

• raw_extract -> "123456"
• raw_extract_length -> 8
• matches_no_quotes -> true
• matches_with_quotes -> false
• matches_wildcard_dbl_quotes -> true

As you can see, there's basically two return values: one with the double-quotes included and one without. LENGTH(), the immediate return value, and the LIKE operators act as though the double-quotes exist. Equality (=) does not act as though the quotes exist.

My best theory is that the direct return type is not VARCHAR and is instead some sort of JSON datatype, but the MySQL documentation didn't confirm or deny that. As someone encountering this for the first time, this contradictory behavior is very confusing. Can anyone shed some light on it?

Answer 1

My best theory is that the direct return type is not VARCHAR and is instead some sort of JSON datatype, but the MySQL documentation didn't confirm or deny that.

That's correct, JSON_EXTRACT() returns a column of type JSON, even though it looks like a string, and the client may cast it to a string.

You can confirm this with the MySQL command-line tool by examining the query result data type:

$ mysql --column-type-info test

mysql> select json_extract(jsontext, '$.myfield') as myfield from mytable;

Field   1:  `myfield`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       JSON
Collation:  utf8mb4_0900_ai_ci (255)
Length:     4294967292
Max_length: 8
Decimals:   31
Flags:      BINARY 

+----------+
| myfield  |
+----------+
| "123456" |
+----------+

The JSON_UNQUOTE() function can transform the JSON value to a more traditional string or integer type.

mysql> select json_unquote(json_extract(jsontext, '$.myfield')) as myfield from mytable;

Field   1:  `myfield`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  utf8mb4_0900_ai_ci (255)
Length:     4294967295
Max_length: 6
Decimals:   31
Flags:      BINARY 

+---------+
| myfield |
+---------+
| 123456  |
+---------+

This means in your test query:

json_extract(jsontext, '$.myfield') raw_extract

Returns type JSON, as described above.

LENGTH(json_extract(jsontext, '$.myfield')) raw_extract_length

The JSON value is cast back to a string before it is passed as an argument to LENGTH(), and then LENGTH() returns 8, which is the number of characters in that string, including the " characters.

CASE WHEN json_extract(jsontext, '$.myfield') = '123456' THEN 'true' ELSE 'false' END matches_no_quotes

This one is wacky. The string scalar '123456' is cast as a JSON value, then they are compared. This is documented:

For comparison of JSON and non-JSON values, the non-JSON value is converted to JSON according to the rules in the following table, then the values compared as described previously.

CASE WHEN json_extract(jsontext, '$.myfield') = '"123456"' THEN 'true' ELSE 'false' END matches_with_quotes

This returnse false, which is unexpected. This seems to show that the implicit casting of '"123456"' to JSON isn't the same as using CAST() explicitly.

We can try explicitly casting the JSON to a string, or the string to JSON, and it gives different results:

cast(json_extract(jsontext, '$.myfield') as char) = '"123456"' as cast_json_to_string

Returns true.

json_extract(jsontext, '$.myfield') = cast('"123456"' as json) as cast_string_to_json

Returns true.

CASE WHEN json_extract(jsontext, '$.myfield') LIKE '"%"' THEN 'true' ELSE 'false' END matches_wildcard_dbl_quotes

I'm not sure what's up with LIKE. It isn't covered in the documentation I linked to. It seems to coerce the JSON value to a string, including the " characters, then apply the LIKE pattern matching.

All I can say is that using JSON in MySQL is fraught with complexity. It's a great example of the Law of Leaky Abstractions. I avoid using JSON as a data type whenever possible.