I want to use the json_search function in MySQL to query data. But find the search is case-sensitive.
For example:
SET @j = '[{"x":"Abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@j, 'one', 'ABC');
This select will return the null. But I need it return "x":"Abc" to me.
Is there any way I can make the search not case-sensitive? So I can get the result $[0].x
The trick is to make the JSON lower case before you search it:
SELECT JSON_SEARCH(LOWER(@j), 'one', LOWER('ABC'));
See dbfiddle.uk for variations.