right way to use calculated value in stored procedure with mysql

Question

I have a JSON object in my DataBase

{
  "3a232f53-7837-49ff-85af-488f6f233ba0" : {
    "exam" : 15,
    "tools" : 5,
    "practice" : 30,
    "intro" : 5,
    "dictionary" : 5,
    "class" : 6
  },
  "2fa94eb2-7f6a-4c59-80d1-e63338be2121" : {
    "exam" : 15,
    "tools" : 5,
    "practice" : 30,
    "intro" : 5,
    "dictionary" : 5,
    "class" : 40
  },
  "33689c5f-60db-44db-80c5-c5ad0e76ba44" : {
    "exam" : 15,
    "tools" : 5,
    "intro" : 5,
    "dictionary" : 5,
    "class" : 29
  }
}

Now I want to make some calculation on it so I wrote a function

CREATE DEFINER=`root`@`localhost` FUNCTION `calculateOutput`(input_data JSON) RETURNS int
    DETERMINISTIC
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE lesson_key CHAR(50);
    DECLARE indx INT DEFAULT 0;

    SET @amount = JSON_LENGTH(JSON_KEYS(input_data));
    
    WHILE indx < @amount DO
        IF JSON_EXTRACT(JSON_EXTRACT(input_data, JSON_EXTRACT(JSON_KEYS(input_data), '$.index')), '$.class') >= 28 THEN
            SET result = result + 1;
        END IF;
        SET indx = indx + 1;

    END WHILE;
    
return result;   
#return JSON_EXTRACT(JSON_EXTRACT(input_data, '$."33689c5f-60db-44db-80c5-c5ad0e76ba44"'), '$.class');
END

But got 0 instead of 2 Could you explain me what I'm doing wrong here? If I pass ID as hardcoded value same code work well

return JSON_EXTRACT(JSON_EXTRACT(input_data, '$."33689c5f-60db-44db-80c5-c5ad0e76ba44"'), '$.class');

score 0 · Answer 1 · answered Dec 16 '22 at 10:58

Finaly I did it

CREATE DEFINER=`root`@`localhost` FUNCTION `calculateAssignments`(input_data JSON) RETURNS int
    DETERMINISTIC
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE lesson_key VARCHAR(50);
    DECLARE indx INT DEFAULT 0;

    SET @amount = JSON_LENGTH(JSON_KEYS(input_data));
    
    WHILE indx < @amount DO
        SET lesson_key = JSON_EXTRACT(JSON_KEYS(input_data), CONCAT('$[',indx,']'));
        IF JSON_EXTRACT(JSON_EXTRACT(input_data, CONCAT('$.',lesson_key)), '$.practice') >= 28 THEN
            SET result = result + 1;
        END IF;
        SET indx = indx + 1;

    END WHILE;
return result;   
END

the reason why it does't work before was here in defining path variable

CONCAT('$.',lesson_key)

right way to use calculated value in stored procedure with mysql

1 Answers1