MYSQL Triggers: JSON_SEARCH an integer value in a json array of integers

Question

I am looking to use json_search to get the array path that corresponds to a value.

I have tried and this works:

SET @j = '["3", "2", "1"]';
SELECT json_search(@j, 'one', '2');

returns $[1];

I have tried and this doesn't work: (How do I make this work?)

SET @j = '[3, 2, 1]';
SELECT json_search(@j, 'one', 2);

returns null;

Basically I want to store @j as an integer array instead of a string array for indexing purposes. Is there any way I can change the integer array into a string array for comparison if there is no way for json_search to work with integers?

Answer 1

It is by design, although I can not agree with mySQL team. This should be implemented.

https://bugs.mysql.com/bug.php?id=79233 [closed]

The specification at https://dev.mysql.com/worklog/task/?id=7909 says: "This function returns path(s) to the given string. The returned path(s) identify object members or array slots which are character strings."

So it seems that the intention of this function was to search for strings. The documentation should be updated to clarify that the function is for searching for string scalars, not for scalars in general.