json_extract mysql doesn't work as intended when comparing string and int types

Question

I am trying to extract the value of a key in a json and it isn't being recognized properly :

This is what I expected :

mysql> SET @json_ = '{"year":"2022"}';
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT JSON_EXTRACT(@json_, "$.year");
+--------------------------------+
| JSON_EXTRACT(@json_, "$.year") |
+--------------------------------+
| "2022"                         |
+--------------------------------+

This isn't what I expected:

mysql> SELECT JSON_EXTRACT(@json_, "$.year") = 2022;
+---------------------------------------+
| JSON_EXTRACT(@json_, "$.year") = 2022 |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

And

mysql> SELECT "2022" = 2022;
+---------------+
| "2022" = 2022 |
+---------------+
|             1 |
+---------------+

Answer 1

That has to do with internal conversion and when it is triggered, make this simple trick for numbers which forces the conversion

SET @json_ = '{"year":"2022"}';
SELECT JSON_EXTRACT(@json_, "$.year") + 0 = 2022;

✓

| JSON_EXTRACT(@json_, "$.year") + 0 = 2022 |
| ----------------------------------------: |
|                                         1 |

db<>fiddle here

Answer 2

Notice the double-quotes around "2022" when you extract it. JSON_EXTRACT() returns a JSON value, not a string or integer. A quoted scalar string is a valid JSON value.

You can use JSON_UNQUOTE() to get the scalar value:

mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(@json_, "$.year")) = 2022;
+-----------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(@json_, "$.year")) = 2022 |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+

The trick shown in nbk's answer works only for integers. Unquoting works if you are trying to extract a string value.