Description:
I have a table with json column ,the value type is string. when I use double quotation marks of query key . when query key is one ,i got empty set but got all result when query key is more than one in sql.
How to repeat:
CREATE TABLE testjson (jdoc JSON);
INSERT INTO testjson VALUES('{"key1": "111", "key2": "222"}');
INSERT INTO testJson VALUES('{"key1": "333", "key2": "444"}');
select * from testjson where JSON_EXTRACT(jdoc,'$.key1') IN ('"111"') ;
return: Empty set (0.00 sec)
select * from testjson where JSON_EXTRACT(jdoc,'$.key1') IN ('"111"', '"333"') ;
return:
+--------------------------------+
| jdoc |
+--------------------------------+
| {"key1": "111", "key2": "222"} |
| {"key1": "333", "key2": "444"} |
+--------------------------------+
select * from testjson where JSON_EXTRACT(jdoc,'$.key1') IN ('111') ;
+--------------------------------+
| jdoc |
+--------------------------------+
| {"key1": "111", "key2": "222"} |
+--------------------------------+