Oracle JSON_ARRAYAGG doesn't support DISTINCT keyword

Question

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?

Answer 1

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:

KEY1	KEY2	FOO	BAR
1	2	["1.0","2.0"]	["A"]
3	4	["2.0"]	["A","B"]

fiddle

Answer 2

Yeah thats not implemented (yet). The workaround is to de-dup the data in advance, eg

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"]

Answer 3

In addition to what Connor wrote alternative workarounds could be

aggregate function + row_number for de-duplication (rn can be moved into where clause instead of decode depending on circumstances but in such scenario distinct is more preferable)

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;

nested aggregates in a correlated scalar (keep in mind that each correlated scalar is, in fact, an implicit 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;

Answer 4

Another solution, also applicable for LISTAGG on older ORACLE versions not supporting DISTINCT, using the fact that NULL values are not taken into account is to DECODE with LAG:

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
;