你想要按索引相加这些值。以下是一个示例 函数，可以实现这个功能
CREATE OR REPLACE FUNCTION sum_json_array(json_clob CLOB) RETURN NUMBER IS
json_array DM_JSON_ARRAY_T;
sum_value NUMBER := 0;
json_element DM_JSON_OBJECT_T;
value NUMBER;
BEGIN
-- 将 CLOB 转换为 JSON 数组
json_array := DM_JSON.parse_array(json_clob);

-- 遍历 JSON 数组
FOR i IN 1..json_array.get_size LOOP
-- 获取当前 JSON 对象
json_element := json_array.get_object(i - 1);

-- 获取 "value" 字段的值
value := json_element.get_number('value');

-- 累加值
sum_value := sum_value + value;
END LOOP;

RETURN sum_value;
END;
/

假设你有一个包含 JSON 数组的 CLOB 列，你可以调用这个函数来计算总和：
SELECT sum_json_array('[
{"value": 10},
{"value": 20},
{"value": 30}
]') AS total_sum
FROM dual;

试着写了个聚合函数，不知道是否符合你的需求，你看看

DROP FUNCTION IF EXISTS JSON_LIST_AGG;  

CREATE OR REPLACE TYPE JSON_LIST_AGG_TYPE AS OBJECT (
  JSON_LIST_AGG_RESULT CLOB,
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT JSON_LIST_AGG_TYPE)   RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT JSON_LIST_AGG_TYPE, VALUE IN CLOB) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN JSON_LIST_AGG_TYPE, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT JSON_LIST_AGG_TYPE, CTX2 IN JSON_LIST_AGG_TYPE) RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY JSON_LIST_AGG_TYPE IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT JSON_LIST_AGG_TYPE) RETURN NUMBER IS
  BEGIN
    SCTX := JSON_LIST_AGG_TYPE(NULL);
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT JSON_LIST_AGG_TYPE, VALUE IN CLOB) RETURN NUMBER IS
    V_LEN INTEGER;
    V_VALPRE NUMBER;
    V_VALNEW NUMBER;
  BEGIN
    V_LEN := JSON_LENGTH(VALUE);
    IF V_LEN <= 0 THEN
      RETURN ODCICONST.SUCCESS;
    END IF;
    IF SELF.JSON_LIST_AGG_RESULT IS NULL THEN
      SELF.JSON_LIST_AGG_RESULT := '[]';
    END IF;
    FOR I IN 1..V_LEN LOOP
      V_VALNEW := JSON_VALUE(VALUE,'$['||I - 1||']');
      IF V_VALNEW IS NOT NULL THEN
        V_VALPRE := NVL(JSON_VALUE(SELF.JSON_LIST_AGG_RESULT,'$['||I - 1||']'),0);
        V_VALNEW := V_VALPRE + V_VALNEW;
        SELF.JSON_LIST_AGG_RESULT := JSON_SET(SELF.JSON_LIST_AGG_RESULT,'$['||I - 1||']',V_VALNEW);
      END IF;
    END LOOP;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN JSON_LIST_AGG_TYPE, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := SELF.JSON_LIST_AGG_RESULT;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT JSON_LIST_AGG_TYPE, CTX2 IN JSON_LIST_AGG_TYPE) RETURN NUMBER IS
    V_LEN INTEGER;
    V_VALPRE NUMBER;
    V_VALNEW NUMBER;
  BEGIN
    V_LEN := JSON_LENGTH(CTX2.JSON_LIST_AGG_RESULT);
    IF V_LEN <= 0 THEN
      RETURN ODCICONST.SUCCESS;
    END IF;
    IF SELF.JSON_LIST_AGG_RESULT IS NULL THEN
      SELF.JSON_LIST_AGG_RESULT := '[]';
    END IF;
    FOR I IN 1..V_LEN LOOP
      V_VALNEW := JSON_VALUE(CTX2.JSON_LIST_AGG_RESULT,'$['||I - 1||']');
      IF V_VALNEW IS NOT NULL THEN
        V_VALPRE := NVL(JSON_VALUE(SELF.JSON_LIST_AGG_RESULT,'$['||I - 1||']'),0);
        V_VALNEW := V_VALPRE + V_VALNEW;
        SELF.JSON_LIST_AGG_RESULT := JSON_SET(SELF.JSON_LIST_AGG_RESULT,'$['||I - 1||']',V_VALNEW);
      END IF;
    END LOOP;
  
    RETURN ODCICONST.SUCCESS;
  END;
END;

CREATE OR REPLACE FUNCTION JSON_LIST_AGG(INPUT CLOB) RETURN CLOB
  PARALLEL_ENABLE AGGREGATE USING JSON_LIST_AGG_TYPE;

测试过程如下：

--创建测试表
DROP TABLE IF EXISTS T_JATST;
CREATE TABLE T_JATST
(
VAL VARCHAR2(1000)
);
--初始化测试数据
INSERT INTO T_JATST VALUES('[1,2,3,4,5,6,7,8]');
INSERT INTO T_JATST VALUES('[1000,2000,3000,4000,5000,6000,7000,8000,9000]');
INSERT INTO T_JATST VALUES('[11,21,31,41,51,61,71,81,91,101,111]');
INSERT INTO T_JATST VALUES('[11,21,31,41,51,61,71,81]');
COMMIT;

--全表聚合
SELECT TO_CHAR(JSON_LIST_AGG(VAL))
  FROM T_JATST;

--结果值：[1023,2044,3065,4086,5107,6128,7149,8170,9091,101,111]

--开窗汇总
SELECT TO_CHAR(JSON_LIST_AGG(VAL) OVER (PARTITION BY 1 ORDER BY VAL))
      ,* 
  FROM T_JATST
/*
返回结果
[1,2,3,4,5,6,7,8]                                        [1,2,3,4,5,6,7,8]
[1001,2002,3003,4004,5005,6006,7007,8008,9000]           [1000,2000,3000,4000,5000,6000,7000,8000,9000]
[1012,2023,3034,4045,5056,6067,7078,8089,9091,101,111]   [11,21,31,41,51,61,71,81,91,101,111]
[1023,2044,3065,4086,5107,6128,7149,8170,9091,101,111]   [11,21,31,41,51,61,71,81]
*/