Oracle JSON_ARRAYAGG不支持DISTINCT关键字。

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

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

英文: 
  1. SELECT key1, key2, JSON_ARRAYAGG(foo) foo, JSON_ARRAYAGG(bar) bar FROM (
  2. select 1 as key1, 2 as key2, '1.0' as foo, 'A' as bar from dual
  3. UNION 
  4. select 1, 2, '2.0' , 'A' as bar from dual
  5. UNION 
  6. select 3, 4, '2.0' , 'A' as bar from dual
  7. UNION 
  8. select 3, 4, '2.0' , 'B' as bar from dual
  9. UNION 
  10. select 3, 4, '2.0' , 'B' as bar from dual) z
  11. GROUP BY key1, key2

The query returns following result:

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

I was expecting

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

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

答案1

得分: 2

你可以使用 COLLECT(DISTINCT ...) 来执行聚合，然后将生成的集合转换为 JSON：

  1. SELECT key1,
  2.        key2,
  3.        ( SELECT JSON_ARRAYAGG(COLUMN_VALUE)
  4.          FROM   TABLE(t.foos) ) AS foo,
  5.        ( SELECT JSON_ARRAYAGG(COLUMN_VALUE)
  6.          FROM   TABLE(t.bars) ) AS bar
  7. FROM   (
  8.   SELECT key1,
  9.          key2,
  10.          CAST(COLLECT(DISTINCT foo) AS SYS.ODCIVARCHAR2LIST) AS foos,
  11.          CAST(COLLECT(DISTINCT bar) AS SYS.ODCIVARCHAR2LIST) AS bars
  12.   FROM   table_name
  13.   GROUP BY key1, key2
  14. ) t

对于示例数据：

  1. CREATE TABLE table_name (
  2.   key1 NUMBER,
  3.   key2 NUMBER,
  4.   foo  VARCHAR2(20),
  5.   bar  VARCHAR2(20)
  6. );
  8. INSERT INTO table_name (key1, key2, foo, bar)
  9. select 1, 2, '1.0', 'A' from dual UNION ALL
  10. select 1, 2, '2.0', 'A' from dual UNION ALL
  11. select 3, 4, '2.0', 'A' from dual UNION ALL
  12. select 3, 4, '2.0', 'B' from dual UNION ALL
  13. 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:

  1. SELECT key1,
  2.        key2,
  3.        ( SELECT JSON_ARRAYAGG(COLUMN_VALUE)
  4.          FROM   TABLE(t.foos) ) AS foo,
  5.        ( SELECT JSON_ARRAYAGG(COLUMN_VALUE)
  6.          FROM   TABLE(t.bars) ) AS bar
  7. FROM   (
  8.   SELECT key1,
  9.          key2,
  10.          CAST(COLLECT(DISTINCT foo) AS SYS.ODCIVARCHAR2LIST) AS foos,
  11.          CAST(COLLECT(DISTINCT bar) AS SYS.ODCIVARCHAR2LIST) AS bars
  12.   FROM   table_name
  13.   GROUP BY key1, key2
  14. ) t

Which, for the sample data:

  1. CREATE TABLE table_name (
  2.   key1 NUMBER,
  3.   key2 NUMBER,
  4.   foo  VARCHAR2(20),
  5.   bar  VARCHAR2(20)
  6. );
  8. INSERT INTO table_name (key1, key2, foo, bar)
  9. select 1, 2, '1.0', 'A' from dual UNION ALL
  10. select 1, 2, '2.0', 'A' from dual UNION ALL
  11. select 3, 4, '2.0', 'A' from dual UNION ALL
  12. select 3, 4, '2.0', 'B' from dual UNION ALL
  13. 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

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

  1. 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;
英文:

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

  1. SQL> with x as (
  2.   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.   3      union all
  4.   4      select 2, 'D' from dual union all select 2, 'D' from dual union all select 2, 'E' from dual
  5.   5      union all
  6.   6      select 3, 'G' from dual union all select 3, 'H' from dual union all select 3, 'H' from dual
  7.   7      )
  8.   8  select x.i,
  9.   9      listagg(distinct x.j, ', ') within group (order by x.j) as X_LIST,
  10.  10      json_arrayagg( x.j order by x.j) as X_JSON
  11.  11  from  x
  12.  12  group by x.i;
  14.          I X_LIST                         X_JSON
  15. ---------- ------------------------------ ------------------------------
  16.          1 A                              ["A","A","A"]
  17.          2 D, E                           ["D","D","E"]
  18.          3 G, H                           ["G","H","H"]
  20. SQL>
  21. SQL> with x as (
  22.   2      select 1 as i, 'A' as j from dual union all select 1, 'A' from dual union all select 1, 'A' from dual
  23.   3      union all
  24.   4      select 2, 'D' from dual union all select 2, 'D' from dual union all select 2, 'E' from dual
  25.   5      union all
  26.   6      select 3, 'G' from dual union all select 3, 'H' from dual union all select 3, 'H' from dual
  27.   7      )
  28.   8  select x.i,
  29.   9      listagg(distinct x.j, ', ') within group (order by x.j) as X_LIST,
  30.  10      json_arrayagg( x.j order by x.j) as X_JSON
  31.  11  from ( select distinct i,j from x order by i,j ) x
  32.  12  group by x.i;
  34.          I X_LIST                         X_JSON
  35. ---------- ------------------------------ ------------------------------
  36.          1 A                              ["A"]
  37.          2 D, E                           ["D","E"]
  38.          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:

  1. SELECT key1, key2, JSON_ARRAYAGG(foo) foo, JSON_ARRAYAGG(bar) bar FROM (
  2. 	select key1, key2, 
  3. 		decode( foo, lag(foo) over(partition by key1, key2 order by foo), null, foo) as foo,
  4. 		decode( bar, lag(bar) over(partition by key1, key2 order by bar), null, bar) as bar
  5. 	from (
  6. 		select 1 as key1, 2 as key2, '1.0' as foo, 'A' as bar from dual
  7. 		UNION 
  8. 		select 1, 2, '2.0' , 'A' as bar from dual
  9. 		UNION 
  10. 		select 3, 4, '2.0' , 'A' as bar from dual
  11. 		UNION 
  12. 		select 3, 4, '2.0' , 'B' as bar from dual
  13. 		UNION 
  14. 		select 3, 4, '2.0' , 'B' as bar from dual
  15. 	)
  16. ) z
  17. GROUP BY key1, key2
  18. ;

答案4

得分: 0

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

使用聚合函数和row_number进行去重（根据情况，rn可以移到where clause中而不是decode，但在这种情况下，distinct更可取）

  1. select 
  2.     x.i,
  3.     listagg(decode(rn, 1, x.j), ', ') within group (order by x.j) as X_LIST,
  4.     json_arrayagg(decode(rn, 1, x.j) order by x.j) as X_JSON
  5. from (select x.*, row_number() over (partition by i, j order by '') rn from x) x
  6. group by x.i;

在相关标量中嵌套聚合函数（请注意，每个相关标量实际上都是隐式的join）

  1. select 
  2.     xx.*,
  3.     (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, 
  4.     (select json_arrayagg(min(x.j) order by x.j) from x where x.i = xx.i group by x.j) as X_JSON
  5. 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)

  1. select 
  2.     x.i,
  3.     listagg(decode(rn, 1, x.j), ', ') within group (order by x.j) as X_LIST,
  4.     json_arrayagg(decode(rn, 1, x.j) order by x.j) as X_JSON
  5. from (select x.*, row_number() over (partition by i, j order by '') rn from x) x
  6. group by x.i;

nested aggregates in a correlated scalar (keep in mind that each correlated scalar is, in fact, an implicit join)

  1. select 
  2.     xx.*,
  3.     (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, 
  4.     (select json_arrayagg(min(x.j) order by x.j) from x where x.i = xx.i group by x.j) as X_LIST
  5. from (select distinct i from x) xx;

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

发表评论

匿名网友

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

确定