您可以使用COLLECT(DISTINCT ...)执行聚合，然后将生成的集合转换为JSON：

SELECT key1,
       key2,
       ( SELECT JSON_ARRAYAGG(COLUMN_VALUE)
         FROM   TABLE(t.foos) ) AS foo,
       ( SELECT JSON_ARRAYAGG(COLUMN_VALUE)
         FROM   TABLE(t.bars) ) AS bar
FROM   (
  SELECT key1,
         key2,
         CAST(COLLECT(DISTINCT foo) AS SYS.ODCIVARCHAR2LIST) AS foos,
         CAST(COLLECT(DISTINCT bar) AS SYS.ODCIVARCHAR2LIST) AS bars
  FROM   table_name
  GROUP BY key1, key2
) t

其中，对于样本数据：

CREATE TABLE table_name (
  key1 NUMBER,
  key2 NUMBER,
  foo  VARCHAR2(20),
  bar  VARCHAR2(20)
);

INSERT INTO table_name (key1, key2, foo, bar)
select 1, 2, '1.0', 'A' from dual UNION ALL
select 1, 2, '2.0', 'A' from dual UNION ALL
select 3, 4, '2.0', 'A' from dual UNION ALL
select 3, 4, '2.0', 'B' from dual UNION ALL
select 3, 4, '2.0', 'B' from dual;

输出：
| KEY1| KEY2| FOO| BAR|
| --------------|--------------|--------------|--------------|
| 1| 2| [“1.0”，“2.0”]|[A]|
| 3|四|[“2.0”]|[“A”，“B”]|
fiddle

是的，还没有实施（尚未）。解决方法是提前消除重复数据，例如

SQL> with x as (
  2      select 1 as i, 'A' as j from dual union all select 1, 'A' from dual union all select 1, 'A' from dual
  3      union all
  4      select 2, 'D' from dual union all select 2, 'D' from dual union all select 2, 'E' from dual
  5      union all
  6      select 3, 'G' from dual union all select 3, 'H' from dual union all select 3, 'H' from dual
  7      )
  8  select x.i,
  9      listagg(distinct x.j, ', ') within group (order by x.j) as X_LIST,
 10      json_arrayagg( x.j order by x.j) as X_JSON
 11  from  x
 12  group by x.i;

         I X_LIST                         X_JSON
---------- ------------------------------ ------------------------------
         1 A                              ["A","A","A"]
         2 D, E                           ["D","D","E"]
         3 G, H                           ["G","H","H"]

SQL>
SQL> with x as (
  2      select 1 as i, 'A' as j from dual union all select 1, 'A' from dual union all select 1, 'A' from dual
  3      union all
  4      select 2, 'D' from dual union all select 2, 'D' from dual union all select 2, 'E' from dual
  5      union all
  6      select 3, 'G' from dual union all select 3, 'H' from dual union all select 3, 'H' from dual
  7      )
  8  select x.i,
  9      listagg(distinct x.j, ', ') within group (order by x.j) as X_LIST,
 10      json_arrayagg( x.j order by x.j) as X_JSON
 11  from ( select distinct i,j from x order by i,j ) x
 12  group by x.i;

         I X_LIST                         X_JSON
---------- ------------------------------ ------------------------------
         1 A                              ["A"]
         2 D, E                           ["D","E"]
         3 G, H                           ["G","H"]

除了康纳写的替代解决方案可能是
聚合函数+ row_number用于重复数据消除（rn可根据具体情况移动到where clause而不是decode，但在这种情况下，distinct更可取）

select 
    x.i,
    listagg(decode(rn, 1, x.j), ', ') within group (order by x.j) as X_LIST,
    json_arrayagg(decode(rn, 1, x.j) order by x.j) as X_JSON
from (select x.*, row_number() over (partition by i, j order by '') rn from x) x
group by x.i;

相关标量中的嵌套聚合（请记住，每个相关标量实际上是一个隐式join）

select 
    xx.*,
    (select listagg(min(x.j), ', ') within group (order by x.j) from x where x.i = xx.i group by x.j) as X_LIST, 
    (select json_arrayagg(min(x.j) order by x.j) from x where x.i = xx.i group by x.j) as X_LIST
from (select distinct i from x) xx;

另一个解决方案，也适用于不支持DISTINCT的旧版本ORACLE上的LISTAGG，使用不考虑NULL值的事实，是使用LAG进行DECODE：

SELECT key1, key2, JSON_ARRAYAGG(foo) foo, JSON_ARRAYAGG(bar) bar FROM (
    select key1, key2, 
        decode( foo, lag(foo) over(partition by key1, key2 order by foo), null, foo) as foo,
        decode( bar, lag(bar) over(partition by key1, key2 order by bar), null, bar) as bar
    from (
        select 1 as key1, 2 as key2, '1.0' as foo, 'A' as bar from dual
        UNION 
        select 1, 2, '2.0' , 'A' as bar from dual
        UNION 
        select 3, 4, '2.0' , 'A' as bar from dual
        UNION 
        select 3, 4, '2.0' , 'B' as bar from dual
        UNION 
        select 3, 4, '2.0' , 'B' as bar from dual
    )
) z
GROUP BY key1, key2
;