postgresql数组查询(无法累积不同维度的数组)

nzkunb0c  于 2022-12-12  发布在  PostgreSQL
关注(0)|答案(1)|浏览(259)
select * from temp10

k    v
ㅡㅡㅡㅡㅡㅡ
a   {a,b}
a   {c}
b   {d,e}
b   {f}

to

k    v
ㅡㅡㅡㅡㅡㅡ
a   {a,b,c}
b   {d,e,f}

I want these results.
my try ↓

select k,json_agg(v) 
from temp10
group by k 
=
k    v
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
a   [["a","b"],  ["c"]]
b   [["d","e"],  ["f"]]

select k,array_agg(v) 
from temp10
group by k 
=
SQL Error [2202E]: ERROR: cannot accumulate arrays of different dimensionality

help me
Is there anything lacking in my explanation? Is there anything lacking in my explanation? Is there anything lacking in my explanation?

gkn4icbw

gkn4icbw1#

You can flatten v and aggregate as a new array -

SELECT k, ARRAY_AGG(v) FROM 
(SELECT k,UNNEST(v) v FROM temp10) test
GROUP BY k

相关问题