PostgreSQL错误:无法累积不同维度的数组

问题在于 ARRAY_AGG(a) 在嵌套子查询中。这将构建一个多维数组,对于该数组,需要输入中具有相同基数的数组。请参阅:

• Is there something like a zip() function in PostgreSQL that combines two arrays?

Postgres的基本困难在于,数组类型可以包含任意数组维度,但多维数组必须为每个维度都有匹配的范围。

这件事有很多细节。空值?空数组?重复?保留阵列位置。。。。

先解开

比你的情况更简单的情况可以通过简单地先取消刺来解决。但你 也 想加一个总数。您可以运行两个单独的聚合:

WITH results(n,a,q) AS (
   VALUES
     ('foo', '{1,2}'::int[], 1)
   , ('foo', '{3,4,5}', 1)
   )
SELECT n, a, q
FROM (
   SELECT n, sum(q) AS q
   FROM   results
   GROUP  BY n
   ) q
FULL JOIN (
   SELECT n, array_agg(elem) AS a
   FROM   results, unnest(a) AS elem
   GROUP  BY n
   ) a USING (n);

使查询复杂化,但只使用基本工具。

自定义聚合函数(我的选择!)

或者,创建一个自定义聚合函数 简单地concats数组 -尺寸匹配!

每个数据库创建一次:

CREATE AGGREGATE array_concat (anycompatiblearray) (
  sfunc = array_cat
, stype = anycompatiblearray
, initcond = '{}'
);

Details in the manual.

然后:

SELECT n, array_concat(a) AS a, sum(q) AS q
FROM  (
    SELECT 'foo' AS n, '{1,2}'::int[] AS a, 1 AS q
    UNION ALL
    SELECT 'foo' AS n, '{3,4,5}'::int[] AS a, 1 AS q
) results
GROUP BY n;

注: 需要匹配数组 尺寸 在输入中。我在小提琴上加了一个违规的例子。
(适用于所有一维数组,如您的示例所示。)

在一个具有大数组的大表上进行的快速测试中,自定义聚合函数是 速度提高10-100倍 比这里的任何解决方法都要先取消数组的嵌套。

解决方法 jsonb

你发布了一个相关的解决方案。这是另一个版本 jsonb_path_query() :

SELECT n
     , ARRAY(SELECT jsonb_path_query(jsonb_agg(a), '$[*][*]')::int) AS a
     , sum(q) AS q
FROM   results
GROUP  BY n;

但它 表现不佳 在测试中。

相关:

• How to turn JSON array into Postgres array?
• Unable to retrieve data from Postgres json column

fiddle

我使用内置的JSON函数回答了自己的问题。

SELECT n, ARRAY(SELECT JSONB_ARRAY_ELEMENTS(JSONB_ARRAY_ELEMENTS(JSONB_AGG(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