By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
create table myjson (val JSON);
| version() |
|---|
| 10.4.7-MariaDB-1:10.4.7+maria~buster |
insert into myjson values (NULL), (true), (false), ('"yes"'), (7), (9.6);
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"}');
select true from DUAL where 'a' LIKE 'A';
select true from DUAL where 'a' LIKE BINARY 'A';
| TRUE |
|---|
| 1 |
select * from myjson where json_extract(val, '$.foo') LIKE BINARY '"bar"';
| val |
|---|
| {"foo": "bar"} |
select * from myjson where json_extract(val, '$.foo') LIKE '"bar"';
| val |
|---|
| {"foo": "bar"} |
select * from myjson where json_extract(val, '$.foo') LIKE BINARY '"bAr"';
-- here goes some magic
select * from myjson where json_extract(val, '$.foo') LIKE 'bAr';
select * from myjson where json_unquote(json_extract(val, '$.foo')) LIKE 'bAr';
| val |
|---|
| {"foo": "bar"} |
select * from myjson where LOWER(json_extract(val, '$.foo')) LIKE LOWER('"bAr"');
| val |
|---|
| {"foo": "bar"} |
select * from myjson where LOWER(json_unquote(json_extract(val, '$.foo'))) = LOWER('bAr');
| val |
|---|
| {"foo": "bar"} |