MariaDB – SUM JSON Array

測試環境為 CentOS 8 X86_64 (虛擬機)

在資料庫一個欄位儲存了 JSON 的陣列 Array [ 0.89, 12.99, 5.23, 2.04 ] 有辦法作加總嗎? 從網路找到寫好的 JSON 陣列加總函數 Function – https://stackoverflow.com/questions/31731513/mysql-labs-json-native-type-how-sum-the-result-of-an-array-returned-by-jsn-extr

DROP FUNCTION IF EXISTS Sum_Array_Cells;
DELIMITER //
CREATE FUNCTION Sum_Array_Cells( input_array json )
RETURNS DOUBLE
 
BEGIN
    DECLARE array_length INT(11);
    DECLARE retval DOUBLE(19,2);
    DECLARE cell_value DOUBLE(19,2);
    DECLARE idx INT(11);
   
    SELECT json_length( input_array ) INTO array_length;

    SET retval = 0.0;

    SET idx = 0;

    WHILE idx < array_length DO
        SELECT json_extract( input_array, concat( '$[', idx, ']' ) )
            INTO cell_value;

        SET retval = retval + cell_value;
        SET idx = idx + 1;
    END WHILE;

    RETURN retval;
	
END//
DELIMITER ;

測試一下.

> SELECT Sum_Array_Cells( '[ 0.89, 12.99, 5.23, 2.04 ]' ) AS SUM_Result;
+------------+
| SUM_Result |
+------------+
|      21.15 |
+------------+
1 row in set (0.000 sec)