Oracle JSON_ARRAYAGG doesn't support DISTINCT keyword

SELECT key1, key2, JSON_ARRAYAGG(foo) foo, JSON_ARRAYAGG(bar) 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

The query returns following result:

1   2   ["1.0","2.0"]   ["A","A"]
3   4   ["2.0","2.0"]   ["A","B"]

I was expecting

1   2   ["1.0","2.0"]   ["A"]
3   4   ["2.0"]         ["A","B"]

I seems that JSON_ARRAYAGG doesn't support DISTINCT, any suggestions?

You can use COLLECT(DISTINCT ...) to perform the aggregation and then convert the generated collection to 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

Which, for the sample data:

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;

Outputs:

fiddle