3

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)

Kiran Muralee
  • 2,068
  • 2
  • 18
  • 25
  • I think you will need to use the result keys from json_search, in case-insensitive manner, in the application code (eg: PHP); using the same approch (`strtolower()`) – Madhur Bhaiya Oct 25 '18 at 12:04
  • @MadhurBhaiya But for doing that I need to take all the long JSON data into the application side (PHP) isn't. Can it be acheived using MySQL query itself and return only the result back into the application. Sorry if I have mistaken your comment. – Kiran Muralee Oct 25 '18 at 12:10
  • No I am not suggesting to take complete JSON to application side. Just take the result (keys here), and use them in case insensitive fashion in PHP code. – Madhur Bhaiya Oct 25 '18 at 12:13
  • @MadhurBhaiya Ok but the result is already case insensitive. What if there is another key like nAMe inside the JSON. Keys 'Name' and 'nAMe' is not the same and contain different values . So again if need to extract value inside key 'Name', it is not possible. – Kiran Muralee Oct 25 '18 at 12:17
  • 1
    @MadhurBhaiya Did you mean to take only the Keys (possibily with values) using case insensitive search and make a small filtering at application side to find out the right Key to use afterwards – Kiran Muralee Oct 25 '18 at 12:36
  • Yes, that is one way to go. – Madhur Bhaiya Oct 25 '18 at 12:37
  • @MadhurBhaiya Originally I needed the path to the string value given ('arun%'), the value may be listed inside nested keys, I need to get the path expression so that after I could make use of the same again in Json_extract to extract the value. – Kiran Muralee Oct 25 '18 at 12:50

1 Answers1

1

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

kgzdev
  • 2,770
  • 2
  • 18
  • 35