In 19.10 json_arrayagg does not abide by the "distinct" keyword (unlike listagg). In 19.14, it throws this error: ORA-30482: DISTINCT option not allowed for this function. Is there another way to accomplish this?
with x as (
select 1 as i, 'A' as j from dual union all select 1, 'A' from dual union all select 1, 'A' from dual
union all
select 2, 'D' from dual union all select 2, 'D' from dual union all select 2, 'E' from dual
union all
select 3, 'G' from dual union all select 3, 'H' from dual union all select 3, 'H' from dual
)
select x.i,
listagg(distinct x.j, ', ') within group (order by x.j) as X_LIST,
json_arrayagg(distinct x.j order by x.j) as X_JSON
from x
group by x.i;
/
Thanx, Don
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"]