MySQL自定义函数处理JSON对象计算结果异常问题排查

数据库中存在如下JSON对象，编写了calculateOutput自定义函数用于统计其中class字段值≥28的条目数量，预期结果为2，但实际返回0；硬编码指定JSON键值时函数可正常运行，现排查错误原因。

数据库中的JSON对象 #

{
  "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
  }
}

原自定义函数代码 #

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

错误原因分析 #

1. JSON键索引提取错误
   循环中JSON_EXTRACT(JSON_KEYS(input_data), '$.index')完全无效：JSON_KEYS返回的是JSON数组，需要通过数组下标$[indx]来获取对应位置的键名，而非字符串$.index。同时提取出的键名带有双引号，必须用JSON_UNQUOTE去除才能正确作为JSON路径的一部分。

2. 会话变量使用不当
   代码使用了会话变量@amount而非局部变量，会话变量会在整个会话周期中存在，可能被其他操作修改导致循环次数出错，应改用局部变量。

3. 嵌套JSON_EXTRACT冗余易出错
   原代码多层嵌套JSON_EXTRACT，可读性差且容易出现路径错误，可通过字符串拼接生成正确JSON路径简化操作。

修正后的函数代码 #

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;
    DECLARE amount INT DEFAULT 0; -- 改用局部变量

    SET amount = JSON_LENGTH(JSON_KEYS(input_data));
    
    WHILE indx < amount DO
        -- 获取当前索引对应的键名，并去除引号
        SET lesson_key = JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(input_data), CONCAT('$[', indx, ']')));
        -- 提取对应键下的class值并判断
        IF JSON_EXTRACT(input_data, CONCAT('$."', lesson_key, '"', '.class')) >= 28 THEN
            SET result = result + 1;
        END IF;
        SET indx = indx + 1;
    END WHILE;
    
    RETURN result;
END

内容的提问来源于stack exchange，提问作者Андрей Измайлов