你可以CROSS JOIN LATERAL一个JSON_TABLE并聚合成：

SELECT t.id,
       t.country,
       t.city,
       j.totalCapacity
FROM   table_name t
       CROSS JOIN LATERAL (
         SELECT SUM(totalCapacity) AS totalCapacity
         FROM   JSON_TABLE(
                  t.capacity,
                  '$[*]'
                  COLUMNS (
                    totalcapacity NUMBER PATH '$.totalCapacity'
                  )
                )
       ) j

字符串
其中，对于样本数据：

CREATE TABLE table_name (
  id       NUMBER,
  country  VARCHAR2(50),
  city     VARCHAR2(50),
  capacity CLOB CHECK (capacity IS JSON)
);

INSERT INTO table_name (
  id,
  country,
  city,
  capacity
) VALUES (
  1,
  'usa',
  'new-york',
  '[{"totalCapacity":100.0},{"totalCapacity":90.0},{"totalCapacity":80.0},{"totalCapacity":80.0}]'
);

型
产出：
| ID|国家|市|总容量|
| --|--|--|--|
| 1 |美国|New-York| 350 |
fiddle

SELECT
    id,
    country,
    city,
    SUM(j.totalCapacity) AS total_capacity
FROM
    A
CROSS APPLY OPENJSON(capacity) WITH (totalCapacity FLOAT '$.totalCapacity') AS j
GROUP BY
    id,
    country,
    city;

字符串

从21开始，你也可以直接在json_value中使用sum（）项函数：

with data(js) as (
    select '[{"totalCapacity":100.0},{"totalCapacity":90.0},{"totalCapacity":80.0},{"totalCapacity":80.0}]' from dual
)
select json_value(js, '$[*].totalCapacity.sum()')
as val
from data
;

val
350

字符串