4

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.

1
  • 2
    If it was mariadb, I'd say just remove JSON_UNQUOTE, but it seems mysql needs it for the 2-4 rows.
    – danblack
    Jul 20, 2021 at 0:13

1 Answer 1

8

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.

2
  • 1
    Well done. I was coming to the same conclusion. JSON_UNQUOTE always returns a varchar. The string "true" when cast down to a integer to compare to false is 0. Ref: fiddle. solution - use cast WHERE CAST(JSON_EXTRACT(my_column, '$.my_value') AS UNSIGNED INTEGER) = false
    – danblack
    Jul 20, 2021 at 0:36
  • Thanks @danblack Nice comments from your part too!. Jul 20, 2021 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.