英文:
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"] |
英文:
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"] |
答案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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论