Why 0/false returns true when using JSON_EXTRACT in MySQL

All we need is an easy explanation of the problem, so here it is.

I have a condition to select a JSON document value that is falsy.

My rows:

1. {"my_value": false}
2. {"my_value": 0}
3. {"my_value": "0"}
4. {"my_value": null}
5. {"my_value": true} // this one got selected

My query:

SELECT * FROM my_table
WHERE JSON_UNQUOTE(JSON_EXTRACT(my_column, '$.my_value')) = false

And 5th row is getting selected even though there’s true value. How can I prevent that?

Version is MySQL 8.0.25.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Hope you’re doing great.

I would like to see your schema to make sure all is good.
I tried to recreate what I believe you did.

Here is what I did:

mysql> create database test295895;
mysql> use test295895;
mysql> create table my_table (my_column JSON);

Then I inserted the values you showed:

mysql> insert into my_table VALUES('{"my_value": false}');
Query OK, 1 row affected (0.01 sec)

mysql> insert into my_table VALUES('{"my_value": 0}');
Query OK, 1 row affected (0.02 sec)

mysql> insert into my_table VALUES('{"my_value": "0"}');
Query OK, 1 row affected (0.02 sec)

mysql> insert into my_table VALUES('{"my_value": null}');
Query OK, 1 row affected (0.02 sec)

mysql> insert into my_table VALUES('{"my_value": true}');
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_table;
+---------------------+
| my_column           |
+---------------------+
| {"my_value": false} |
| {"my_value": 0}     |
| {"my_value": "0"}   |
| {"my_value": null}  |
| {"my_value": true}  |
+---------------------+
5 rows in set (0.00 sec)

Then I ran your query and I saw all the results:

mysql> SELECT * FROM my_table
    -> WHERE JSON_UNQUOTE(JSON_EXTRACT(my_column, '$.my_value')) = false
    -> ;
+---------------------+
| my_column           |
+---------------------+
| {"my_value": false} |
| {"my_value": 0}     |
| {"my_value": "0"}   |
| {"my_value": null}  |
| {"my_value": true}  |
+---------------------+
5 rows in set, 3 warnings (0.00 sec)

I decided to see what JSON_EXTRACT was returning:

mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(my_column, '$.my_value')) from my_table;
+-----------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(my_column, '$.my_value')) |
+-----------------------------------------------------+
| false                                               |
| 0                                                   |
| 0                                                   |
| null                                                |
| true                                                |
+-----------------------------------------------------+
5 rows in set (0.00 sec)

mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(my_column, '$.my_value')) from my_table WHERE JSON_UNQUOTE(JSON_EXTRACT(my_column, '$.my_value')) = false;
+-----------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(my_column, '$.my_value')) |
+-----------------------------------------------------+
| false                                               |
| 0                                                   |
| 0                                                   |
| null                                                |
| true                                                |
+-----------------------------------------------------+
5 rows in set, 3 warnings (0.00 sec)

So at least in my case, it seems that everything returned is being returned as String, so instead of comparing to a boolean, I compared to ‘false’:

mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(my_column, '$.my_value')) from my_table WHERE JSON_UNQUOTE(JSON_EXTRACT(my_column, '$.my_value')) = 'false';
+-----------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(my_column, '$.my_value')) |
+-----------------------------------------------------+
| false                                               |
+-----------------------------------------------------+
1 row in set (0.00 sec)

And it worked.
So maybe can you test if you compare to ‘false’ VARCHAR instead of Boolean it works?.

Cheers.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply