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

Related: [Get path expression of a value inside JSON data using case insensitive search using MySQL Json_search function](https://stackoverflow.com/questions/52988706/get-path-expression-of-a-value-inside-json-data-using-case-insensitive-search-us) — Scratte, Aug 04 '20 at 09:09

score 4 · Answer 1 · edited Oct 02 '20 at 19:07

4

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.

edited Oct 02 '20 at 19:07

Dharman

answered Aug 04 '20 at 08:46

Scratte

1 Answers1