26

I want to take an array of n dimensions and return set containing rows of arrays of n-1 dimensions. 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.

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

CREATE OR REPLACE FUNCTION tstng.unnest2(anyarray)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql IMMUTABLE AS
$$
BEGIN
    RETURN QUERY
    SELECT $1[i]
    FROM generate_series(array_lower($1,1), array_upper($1,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).

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.

Improve this question

3 Answers 3

Reset to default
35

Function

To break out 1-dimensional arrays from n-dimensional arrays - representing leaves of the nested dimensions.

PL/pgSQL

With a FOR loop looping through the array.
Works for any array dimensions and any element type:

CREATE OR REPLACE FUNCTION unnest_nd_1d(a anyarray, OUT a_1d anyarray)
  RETURNS SETOF anyarray
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
BEGIN                -- null is covered by STRICT
   IF a = '{}' THEN  -- empty
      a_1d = '{}';
      RETURN NEXT;
   ELSE              --  all other cases
      FOREACH a_1d SLICE 1 IN ARRAY a LOOP
         RETURN NEXT;
      END LOOP;
   END IF;
END
$func$;

fiddle

SLICE 1 instructs to take 1-dimensonal arrays. (SLICE 2 would take 2-dimensional arrays.)

This returns null on null input (because of the STRICT modifier) and an empty array on empty input (caught by IF). We need to special-case the empty array '{}' because, quoting the manual:

The SLICE value must be an integer constant not larger than the number of dimensions of the array.

PARALLEL SAFE only for Postgres 9.6 or later.

Later tests revealed this PL/pgSQL function to be fastest.
Related:

Pure SQL

Only works for 2D arrays (of any element type):

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

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

fiddle
Old sqlfiddle

Explanation

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. The manual:

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

0 has no special meaning as array subscript. There's just nothing there for Postgres arrays with default array indexes.
Also, with two-dimensional arrays, you need two indexes to get a base element. Like:

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 two dimensions with 3 elements (1 to 3) each (9 base elements).
If you want n-1 dimensions then this is a correct result:

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 one dimension. unnest() produces one base element per row (regardless of array dimensions). Your example is just another 2-dimensional array with a missing set of curly brackets ... ?

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

If you want a slice of the array:

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

Result:

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

Or:

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

Result:

{{4,5}}

To flatten the result (get a 1D array):

Read the manual here.

Postgres 8.4-

In ancient versions of Postgres, array_agg() is not installed, yet. Create it first:

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

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

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

Call:

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

Result

{1,2}
{3,4}
{5,6}
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

I was able to work from 0 having no special meaning, thank you.
When I run SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1][2] I get 2 not 1. And 2 is exactly what I expected. Why 1? Is it a mistake? Or am I missing something? Same with SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[2:2][3]; yielding {{4,5,6}}, not {{4,5}} (And by the way the docs recommend using the full [2:2][1:3] notation to make it explicit too, and I agree!)
Hi, in nowdays, with pg10, no news? There are no build-in or efficient workaround to do unnest_2d_1d()?
I wish there was a function in Postgres that allows unnesting any n-dimensional array for 1 level. It's so annoying, that there is no easy way to do this.
@ErwinBrandstetter - dbfiddle.uk/KJKVUBs8
|
11

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.

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)

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

code:

CREATE OR REPLACE FUNCTION unnest_multidim(anyarray)
RETURNS SETOF anyarray AS
$BODY$
  SELECT array_agg($1[series2.i][series2.x]) FROM
    (SELECT generate_series(array_lower($1,2),array_upper($1,2)) as x, series1.i
     FROM 
     (SELECT generate_series(array_lower($1,1),array_upper($1,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.

CREATE OR REPLACE FUNCTION unnest_multidim(anyarray, integer)
  RETURNS SETOF anyarray AS
$BODY$
SELECT array_agg($1[series2.i][series2.x]) FROM
  (SELECT generate_series(array_lower($1,2),array_upper($1,2)) as x, series1.i
  FROM 
    (SELECT CASE WHEN $2  IS NULL THEN
      generate_series(array_lower($1,1),array_upper($1,1)) 
      ELSE $2
      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)
Improve this answer

1 Comment

PotsgreSQL 8.4 introduced array_agg() function that is the standard name for array_accum.
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.