Get path expression of a value inside JSON data using case insensitive search using MySQL Json_search function

user_2200476 0
Viewed 343

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)

mysql-json mysql json
1 Answers

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

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

db-fiddle

user_27942800
Related