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

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 |
 ---------------

CodePudding user response：

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

CodePudding user response：

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.