Oracle JSON_ARRAYAGG不支持DISTINCT关键字。

huangapple go评论65阅读模式
英文:

Oracle JSON_ARRAYAGG doesn't support DISTINCT keyword

问题

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

英文:
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?

答案1

得分: 2

你可以使用 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 4 ["2.0"] ["A","B"]

fiddle

英文:

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

答案2

得分: 1

是的,这部分尚未实现。解决方法是提前去重数据,例如

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(x.j order by x.j) as X_JSON
from x
group by x.i;
英文:

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

答案3

得分: 1

Here is the translated content:

另一种解决方案,也适用于不支持 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
;

英文:

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
;

答案4

得分: 0

除了Connor写的内容之外,还有一些备选方法可以用于解决问题:

使用聚合函数和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_JSON
from (select distinct i from x) xx;

希望这些翻译对你有帮助。如果有任何其他问题,请随时提出。

英文:

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;

huangapple
  • 本文由 发表于 2023年5月17日 08:32:55
  • 转载请务必保留本文链接:https://go.coder-hub.com/76267867.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定