postgresql 将数组取消一层

Unnest array by one level

提问by Matt

I want to take an array of ndimensions and return set containing rows of arrays of n-1dimensions. For example, take the array ARRAY[[1,2,3], [4,5,6], [7,8,9]]and return a set {1,2,3}, {4,5,6}, {7,8,9}. Using unnest returns the set 1,2,3,4,5,6,7,8,9.

我想获取一个n维度数组并返回包含n-1维度数组行的集合。例如，获取数组ARRAY[[1,2,3], [4,5,6], [7,8,9]]并返回一个 set {1,2,3}, {4,5,6}, {7,8,9}。使用 unnest 返回 set 1,2,3,4,5,6,7,8,9。

I tried grabbing the unnest function from PostgreSQL 8.4, which seems like it would do what I'm looking for:

我尝试从 PostgreSQL 8.4 中获取 unnest 函数，这似乎可以满足我的要求：

CREATE OR REPLACE FUNCTION tstng.unnest2(anyarray)
    RETURNS SETOF anyelement
    LANGUAGE plpgsql
    IMMUTABLE
    AS $$
    BEGIN
            RETURN QUERY SELECT [i]
                FROM generate_series(array_lower(,1), array_upper(,1)) i;
        END;
    $$;

However, SELECT tstng.unnest2(ARRAY[[1,2,3], [4,5,6], [7,8,9]]);returns the set , ,(i.e.: 3 null rows).

但是，SELECT tstng.unnest2(ARRAY[[1,2,3], [4,5,6], [7,8,9]]);返回集合, ,（即：3 个空行）。

I've also found that SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[0];returns null, which I believe to be the root of my problem.

我还发现SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[0];返回 null，我认为这是我问题的根源。

回答by Erwin Brandstetter

Explain

解释

SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[0]

returns the same as

返回与

SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[17]

which is NULL. I quote the docs on that matter:

这是NULL。我引用了关于这个问题的文档：

By default, the lower bound index value of an array's dimensions is set to one.

默认情况下，数组维度的下限索引值设置为 1。

0has no special meaning here. Also, with a two-dimensional arrays, you need two indexesto get a base element. Like this:

0在这里没有特别的意义。此外，对于二维数组，您需要两个索引来获取基本元素。像这样：

SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1][2]

Result:

结果：

2

---

---

The first part of your message is a bit unclear.

你的消息的第一部分有点不清楚。

SELECT array_dims(ARRAY[[1,2,3], [4,5,6], [7,8,9]])

Result:

结果：

[1:3][1:3]

That's twodimensions with 3 elements (1 to 3) each (9 base elements).
If you want n-1dimensions then this is a correct result:

这是两个维度，每个维度有 3 个元素（1 到 3 个）（9 个基本元素）。
如果您想要n-1尺寸，那么这是一个正确的结果：

SELECT ARRAY (SELECT unnest('{{1,2,3}, {4,5,6}, {7,8,9}}'::int[]))

Result:

结果：

{1,2,3,4,5,6,7,8,9}

That's onedimension. unnest()always produces one base element per row. I am not sure what result you desire exactly . Your example is just another 2-dimenstional array with a missing set of curly brackets ... ?

那是一维。unnest()每行总是产生一个基本元素。我不确定你想要什么结果。您的示例只是另一个缺少一组大括号的二维数组......？

{1,2,3}, {4,5,6}, {7,8,9}

---

---

If you want a slice of the array, try this notation:

如果你想要一个数组的切片，试试这个表示法：

SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1:2]

Result:

结果：

{{1,2,3},{4,5,6}}

Or this:

或这个：

SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[2:2][1:2]

Result:

结果：

{{4,5}}

To flattenthe result (get a 1D array):

要展平结果（获得一维数组）：

• How to select 1d array from 2d array postgresql

• 如何从二维数组 postgresql 中选择一维数组

Read more in the manual here.

在此处阅读手册中的更多信息。

Function

功能

Later test revealed that this plpgsql function is muchfaster. Requires Postgres 9.1 or later:

后来的测试表明，这个plpgsql函数要快得多。需要 Postgres 9.1 或更高版本：

CREATE OR REPLACE FUNCTION unnest_2d_1d(ANYARRAY, OUT a ANYARRAY)
  RETURNS SETOF ANYARRAY AS
$func$
BEGIN
   FOREACH a SLICE 1 IN ARRAY  LOOP
      RETURN NEXT;
   END LOOP;
END
$func$  LANGUAGE plpgsql IMMUTABLE;

See:

看：

• How to unnest a 2d array into a 1d array quickly in PostgreSQL?

• 如何在 PostgreSQL 中快速将二维数组取消嵌套到一维数组中？

This is an improved and simplified version of the function Lukas posted:

这是Lukas 发布的函数的改进和简化版本：

CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray)
  RETURNS SETOF anyarray AS
$func$
SELECT array_agg([d1][d2])
FROM   generate_subscripts(,1) d1
    ,  generate_subscripts(,2) d2
GROUP  BY d1
ORDER  BY d1
$func$  LANGUAGE sql IMMUTABLE;

For Postgres versions < 8.4, array_agg()is not installed by default. Create it first:

对于低于 8.4 的 Postgres 版本，array_agg()默认情况下不安装。首先创建它：

CREATE AGGREGATE array_agg(anyelement) (
 SFUNC=array_append,
 STYPE=anyarray,
 INITCOND='{}'
);

Also, generate_subscripts()is not born, yet. Use instead:

而且，generate_subscripts()还没有出生。改用：

...
FROM   generate_series(array_lower(,1), array_upper(,1)) d1
    ,  generate_series(array_lower(,2), array_upper(,2)) d2
...

Call:

称呼：

SELECT unnest_2d_1d(ARRAY[[1,2], [3,4], [5,6]]);

Result

结果

{1,2}
{3,4}
{5,6}

SQL Fiddle.

SQL小提琴。

回答by Lukas Eklund

Slices of a multi-dimensional are returned as multi-dimensional arrays. This is a modified version of unnest that will take a 2-dimensional array and return a set of 1-dimensional arrays.

多维切片作为多维数组返回。这是 unnest 的修改版本，它将采用二维数组并返回一组一维数组。

update: modified to use the built-in array_agg aggregate function that was default as of 8.4. (http://www.postgresql.org/docs/9.2/static/functions-aggregate.html)

更新：修改为使用内置的 array_agg 聚合函数，这是 8.4 的默认值。( http://www.postgresql.org/docs/9.2/static/functions-aggregate.html)

Caveats:

注意事项：

• It only works for 2-dimensional arrays (I should probably rename the function to reflect that limitation).
• If you are on 8.3 (and can't upgrade), you need to have the array_accum aggregate defined and change all references in the functions below from array_agg to array_accum. http://www.postgresql.org/docs/8.3/static/xaggr.html

• 它仅适用于二维数组（我可能应该重命名函数以反映该限制）。
• 如果您使用的是 8.3（并且无法升级），则需要定义 array_accum 聚合并将以下函数中的所有引用从 array_agg 更改为 array_accum。http://www.postgresql.org/docs/8.3/static/xaggr.html

code:

代码：

CREATE OR REPLACE FUNCTION unnest_multidim(anyarray)
RETURNS SETOF anyarray AS
$BODY$
  SELECT array_agg([series2.i][series2.x]) FROM
    (SELECT generate_series(array_lower(,2),array_upper(,2)) as x, series1.i
     FROM 
     (SELECT generate_series(array_lower(,1),array_upper(,1)) as i) series1 
    ) series2
GROUP BY series2.i
$BODY$
LANGUAGE sql IMMUTABLE;

Result:

结果：

select unnest_multidim(array[[1,2,3],[4,5,6],[7,8,9]]);
unnest_multidim
----------------------
{1,2,3}
{4,5,6}
{7,8,9}
(3 rows)

Now, let's say for some reason you want easy access to just one of these arrays that is returned. The following function adds an optional index parameter that will return the nested array of the index you provide, or, if you provide null, will output the full set of "unnested" arrays.

现在，假设出于某种原因，您只想轻松访问返回的这些数组之一。以下函数添加了一个可选的 index 参数，该参数将返回您提供的索引的嵌套数组，或者，如果您提供 null，则将输出完整的“未嵌套”数组集。

CREATE OR REPLACE FUNCTION unnest_multidim(anyarray, integer)
  RETURNS SETOF anyarray AS
$BODY$
SELECT array_agg([series2.i][series2.x]) FROM
  (SELECT generate_series(array_lower(,2),array_upper(,2)) as x, series1.i
  FROM 
    (SELECT CASE WHEN   IS NULL THEN
      generate_series(array_lower(,1),array_upper(,1)) 
      ELSE 
      END as i) series1
  ) series2
GROUP BY series2.i
$BODY$
LANGUAGE sql IMMUTABLE;

Results:

结果：

db=> select unnest_multidim(array[[1,2,3],[4,5,6],[7,8,9]],2);
 unnest_multidim 
-----------------
 {4,5,6}
(1 row)

db=> select unnest_multidim(array[[1,2,3],[4,5,6],[7,8,9]],NULL);
 unnest_multidim 
-----------------
 {1,2,3}
 {4,5,6}
 {7,8,9}
(3 rows)

回答by AlonG

A word of caution: when using array_agg on postgres <9 order may change PostgreSQL array_agg orderIf you plan to use the unnested array say for finding argmax, this will corrupt your data.

一个忠告：在使用上的Postgres ARRAY_AGG当<9顺序可能改变 的PostgreSQL ARRAY_AGG顺序如果您计划使用嵌套的数组发言权寻找argmax，这将破坏你的数据。