PostgreSQL聚合不同维度数组报错：cannot accumulate arrays of different dimensionality

问题重现 #

你尝试聚合不同长度的一维数组时，执行以下查询报错：

SELECT n, ARRAY(SELECT UNNEST(a) FROM (SELECT ARRAY_AGG(a) AS a) a) AS a, SUM(q) AS q
FROM (
    SELECT 'foo' AS n, '{1,2}'::integer[] AS a, 1 AS q
    UNION ALL
    SELECT 'foo' AS n, '{3,4,5}'::integer[] AS a, 1 AS q
) results
GROUP BY n

错误提示：

ERROR: cannot accumulate arrays of different dimensionality

期望结果：

当所有数组长度相同时，原查询可以正常执行，但无法处理长度不一致的情况。

解决方案 #

正确的思路是先将每个数组拆分为单个元素，再按分组字段聚合元素为新数组，同时计算求和值：

SELECT 
    n,
    ARRAY_AGG(elem) AS a, -- 若需保持元素顺序，可添加ORDER BY elem
    SUM(q) AS q
FROM (
    SELECT n, UNNEST(a) AS elem, q
    FROM (
        SELECT 'foo' AS n, '{1,2}'::integer[] AS a, 1 AS q
        UNION ALL
        SELECT 'foo' AS n, '{3,4,5}'::integer[] AS a, 1 AS q
    ) results
) unnested
GROUP BY n;

原查询报错原因 #

原查询中ARRAY_AGG(a)会将多个一维数组聚合为二维数组（例如{{1,2},{3,4,5}}），当子数组长度不一致时，PostgreSQL无法生成结构规整的二维数组，因此抛出维度不匹配的错误。而当所有子数组长度相同时，聚合后的二维数组结构一致，所以可以正常执行。

执行结果 #

上述解决方案执行后，将得到你期望的输出：

内容的提问来源于stack exchange，提问作者Oliver Morgan