json_sum_by_col, MySQL

Language: Layout:

DROP TABLE IF EXISTS jsondata;
CREATE TABLE jsondata (json JSON, col varchar(11));

INSERT INTO jsondata VALUES
('[{"key_name" : "key1", "key_value" : 1}, {"key_name" : "key2", "key_value" : 3}]', 'aaa'),
('[{"key_name" : "key1", "key_value" : 0}, {"key_name" : "key3", "key_value" : 2}]', 'bbb'),
('[{"key_name" : "key1", "key_value" : 50}, {"key_name" : "key2", "key_value" : 0}]', 'aaa');

DROP FUNCTION IF EXISTS json_sum_by_col;
CREATE FUNCTION json_sum_by_col(col varchar(100)) RETURNS JSON
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE done INT DEFAULT FALSE;
    DECLARE select_values JSON;
    DECLARE temp_result JSON;
    DECLARE json_result JSON DEFAULT '[]';
    DECLARE temp_key varchar(11);
    DECLARE temp_value int;

DECLARE curs CURSOR FOR SELECT json FROM jsondata WHERE jsondata.col = col;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN curs;
        read_loop: LOOP
            SET i = 0;
            FETCH curs INTO select_values;

IF done THEN
              LEAVE read_loop;
            END IF;

WHILE i < JSON_LENGTH(select_values) DO
                -- extract key and value for i element
                SET temp_key = JSON_EXTRACT(JSON_EXTRACT(select_values, CONCAT('$[',i,']')), '$.key_name');
                SET temp_value = JSON_EXTRACT(JSON_EXTRACT(select_values, CONCAT('$[',i,']')), '$.key_value');

-- search json_result for key
                SET @search = JSON_SEARCH(json_result, 'one', JSON_UNQUOTE(temp_key));
                IF @search IS NOT NULL THEN
                    -- if exists add to existing value
                    SET @value_path = JSON_UNQUOTE(REPLACE(@search, 'name', 'value'));
                    SET temp_value = temp_value + JSON_EXTRACT(json_result, @value_path);
                    SET json_result = JSON_REPLACE(json_result, @value_path, temp_value);
                ELSE
                    -- else attach it to json_result
                    SET temp_result = JSON_OBJECT("key_name", JSON_UNQUOTE(temp_key), "key_value", temp_value);
                    SET json_result = JSON_INSERT(json_result, CONCAT('$[',JSON_LENGTH(json_result),']'), temp_result);
                END IF;

SELECT i + 1 INTO i;
            END WHILE;
        END LOOP;
    CLOSE curs;

RETURN json_result;
END;

SELECT col, json_sum_by_col(col) FROM jsondata GROUP BY col;


absolute service time: 0,35 sec