Why 0/false returns true when using JSON_EXTRACT in MySQL

Question

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.

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