mysql jsonsearch all,使用不区分大小写的搜索(使用MySQL Json_search函数)获取JSON数据内的值的路径表达式...

I have a requirement where by I need to extract JSON path expression from a JSON data searching by a value.

For example if I have the JSON data stored like

SET @j = '[{"name":"Kiran Muralee", "age" : 30 , "Salary" : 30000},

{"Name":"Arun Babu", "age" : 35 , "Salary" : 60000}]';

And if I need to get the JSON Path of value 'Arun Babu' I could use

SELECT JSON_SEARCH(@j, 'all', 'Arun Babu');

This results in

"$[1].Name"

Which is right but the problem is if I search with value 'arun babu', it returns NULL. So to do a case insensitive search I used

SELECT JSON_SEARCH(lower(@j), 'all', lower('arun babu'));

So now I am getting the result as

"$[1].name"

But this is not I needed because now the result also outputted in lower case, I need the result to be

"$[1].Name"

Can anyone suggest a good solution or approach.

MySQL version using is 5.7 (JSON type supported version)

解决方案

We can get the expected solution by using the case insensitive collation:

SELECT JSON_SEARCH(@j , 'all', 'arun babu' COLLATE utf8mb4_general_ci);