Search code examples
mysqlsqljson-extract

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

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