clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 984328 fiddles created (11656 in the last week).

select version(); create table myjson (val JSON);
version()
10.4.7-MariaDB-1:10.4.7+maria~buster
 hidden batch(es)


insert into myjson values (NULL), (true), (false), ('"yes"'), (7), (9.6);
 hidden batch(es)


insert into myjson values ('[]'), ('{}'), ('{"a": "b", "c": 1}'), ('{"a": "b", "c": 1, "d": ["e", {"f": "g"}], "h": true, "i": false, "j": null, "k": {"l": "m"}}'), ('[1, [2]]'), ('{"k": true, "l": false}'), ('{"foo": "bar"}');
 hidden batch(es)


select true from DUAL where 'a' LIKE 'A'; select true from DUAL where 'a' LIKE BINARY 'A';
TRUE
1
TRUE
 hidden batch(es)


select * from myjson where json_extract(val, '$.foo') LIKE BINARY '"bar"';
val
{"foo": "bar"}
 hidden batch(es)


select * from myjson where json_extract(val, '$.foo') LIKE '"bar"';
val
{"foo": "bar"}
 hidden batch(es)


select * from myjson where json_extract(val, '$.foo') LIKE BINARY '"bAr"';
val
 hidden batch(es)


-- here goes some magic select * from myjson where json_extract(val, '$.foo') LIKE 'bAr';
val
 hidden batch(es)


select * from myjson where json_unquote(json_extract(val, '$.foo')) LIKE 'bAr';
val
{"foo": "bar"}
 hidden batch(es)


select * from myjson where LOWER(json_extract(val, '$.foo')) LIKE LOWER('"bAr"');
val
{"foo": "bar"}
 hidden batch(es)


select * from myjson where LOWER(json_unquote(json_extract(val, '$.foo'))) = LOWER('bAr');
val
{"foo": "bar"}
 hidden batch(es)