Question 170009 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Does Oracle JSON_ARRAYAGG 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?

Tags:

2

Answers


  1. 0

    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"]
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search