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

Issue

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

Solution

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

Answered By – nbk

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published