How to make the Json_Search not case-sensitive?

Question

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

user_12695027 · Answer

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.