Iterate through JSON in MySQL query

Question

I'm looking for something like forEach for a JSON array in MySQL.

I manager IDs in MySQL JSON data type like this: [1, 2, 3, 4, 5], and I want to perform an action for each item in the list.

A naive solution is to do a WHILE loop with a counter that starts at 0 and ends when VAR_MANAGER_ID is null. Here is a contrived example of how the inside of the WHILE loop would look:

SET VAR_PATH = CONCAT('$[', COUNTER, ']');
SET VAR_MANAGER_ID = JSON_PARSE(VAR_MANAGER_IDS, PATH);

# See if we've reached the end of the list
IF VAR_MANAGER_ID IS NULL
THEN 
    BREAK
END;
INSERT INTO LU_MANAGER (MANAGER_ID) VALUES (VAR_MANAGER_ID);

But there has to be a better way! How can I do something like:

FOREACH JSON_PARSE(VAR_MANAGER_IDS, '$[*]') AS VAR_MANAGER_ID
    INSERT INTO LU_MANAGER (MANAGER_ID) VALUES (VAR_MANAGER_ID);

Answer 1

complete newbie here but I found a way to iterate a JSON array using REPEAT-UNTIL-END REPEAT

   REPEAT
        SET txt = JSON_EXTRACT(myjson, CONCAT("$[", indx, "]"));
        # use txt
        SET indx = indx + 1;
        UNTIL indx = JSON_LENGTH(myjson)
   END REPEAT;