MYSQL Triggers: JSON_SEARCH - integer value in json array of integers

Question

I am looking to use json_search to get the path to an array that matches the value.

I tried and it works:

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

returns $ [1];

I tried and it does not work: (How do I make this work?)

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

returns zero;

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

Answer 1

This is intended, although I cannot agree with the 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 states: "This function returns the path (s) to the given string. The paths identify the elements of the object or slots of the array that are characters of the string."

So it looks like the purpose of this function was to search for a string. The documentation should be updated to clarify that the function is to search for string scalars, and not for scalars in General.

Answer 2

I tried a workaround method that is CONCAT for a row and returns it in JSON and updates the table. This is not the most elegant way, so if you have any suggestions, please let me know! :)

 BEGIN DECLARE var1 INT default -2; //offsets DECLARE var2 INT default 2; //offsets SELECT StatusID into @statusList FROM UserStatusesIndex; SET @statusID_to_remove = OLD.StatusID; SET @startIndex = INSTR(@statusList, @statusID_to_remove); SET @charLength = CHAR_LENGTH(@statusID_to_remove); IF @startIndex <= 2 THEN SET var1=-1, var2=2; //If its the first index ELSEIF (CHAR_LENGTH(@statusList) - @startIndex <= 2) THEN SET var1=-3, var2=0; //If its the last index ELSE SET var1=-2, var2=2; END IF; SET @newJson=CAST(CONCAT(SUBSTRING(@statusList, 1, @startIndex+var1), SUBSTR(@statusList, @startIndex + @charLength+var2, CHAR_LENGTH(@statusList) - @startIndex - @charLength+2)) as JSON); UPDATE UserStatusesIndex SET StatusID=@newJson WHERE StatusCreator=OLD.StatusCreator; END 

Thanks!

Answer 3

It works:

 SET @j = '[3, 2, 1]'; SELECT JSON_CONTAINS(@j, '3', '$');