Strange behaviour in mysql json_extract with boolean

Question

Here, I first show the untouched result of a boolean json_extract. Then I show comparing it to true. Then comapre again to true after a coalesce.

MySQL [(none)]> select json_extract('{"a": true}', '$.a');
+------------------------------------+
| json_extract('{"a": true}', '$.a') |
+------------------------------------+
| true                               |
+------------------------------------+
1 row in set (0.00 sec)

MySQL [(none)]> select json_extract('{"a": true}', '$.a')=true;
+-----------------------------------------+
| json_extract('{"a": true}', '$.a')=true |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.01 sec)

MySQL [(none)]> select coalesce(json_extract('{"a": true}', '$.a'), 'false')=true;
+------------------------------------------------------------+
| coalesce(json_extract('{"a": true}', '$.a'), 'false')=true |
+------------------------------------------------------------+
|                                                          0 |
+------------------------------------------------------------+
1 row in set (0.01 sec)

As you can see, after the coalesce MySQL doesn't know I am dealing with json stuff there.

Question: How am I supposed to provide a coalesce fallback for my json_extract while at the same time being able to compare the value against true? am I supposed to use json strings select coalesce(json_extract('{"a": true}', '$.a'), 'false')='true'? I don't like that because the same approach doesn't work when not using coalesce:

MySQL [(none)]> select json_extract('{"a": true}', '$.a')='true';
+-------------------------------------------+
| json_extract('{"a": true}', '$.a')='true' |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)

MySQL [(none)]> select coalesce(json_extract('{"a": true}', '$.a'), 'false')='true';
+--------------------------------------------------------------+
| coalesce(json_extract('{"a": true}', '$.a'), 'false')='true' |
+--------------------------------------------------------------+
|                                                            1 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

If someone looks at those queries they will think it is a bug, and if in a future version of mysql this gets "patched" the queries will be broken.

What is the correct way to impelment this coalesce feature?

Answer 1

Just cast back to json whenever you coalesce json.

select cast(coalesce(json_extract('{"a": true}', '$.a'), 'false') as json)=true;