Unnest Array One Level

Question

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

I tried to capture the unsest function from PostgreSQL 8.4, which seems to 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 a set,, (i.e. 3 null strings).

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

Answer 1

To 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 cite documents in this regard :

By default, the index value of the lower boundary of the dimension array is set to one.

0 does not really matter here. Also, with two-dimensional arrays, you need two indexes to get the base element. Like this:

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

Result:

 2 

---

The first part of your message is a bit fuzzy.

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

Result:

 [1:3][1:3] 

These are two dimensions with 3 elements (from 1 to 3) each (9 basic elements).
If you want the dimensions n-1 , then this is the 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} 

This is one dimension. unnest() always creates one base element for each line. I’m not sure which result you want for sure. Your example is another 2-dimensional array with a missing set of curly braces ...?

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

---

If you want to slice an 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 that:

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

Result:

 {{4,5}} 

In smooth out the result (get a 1D array):

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

More details in the manual here .

Functions

A test later showed that this plpgsql function is much faster. Requires Postgres 9.1 or later:

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

Cm:

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

This is an improved and simplified version of the Lukas feature, hosted :

 CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray) RETURNS SETOF anyarray 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$ LANGUAGE sql IMMUTABLE; 

For versions of Postgres <8.4, array_agg() not set by default. First create it:

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

In addition, generate_subscripts() has not yet been born. 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} 

SQL Fiddle

Answer 2

As multidimensional arrays, multidimensional slices are returned. This is a modified version of unsest that will take a 2-dimensional array and return a set of 1-dimensional arrays.

update : changed to use the built-in aggregation function array_agg, which by default was 8.4. ( http://www.postgresql.org/docs/9.2/static/functions-aggregate.html )

Cautions:

• It only works for 2-dimensional arrays (maybe I should rename the function to reflect this limitation).
• If you are at step 8.3 (and cannot update), you need to define the array_accum aggregate and change all the links in the functions below from array_agg to array_accum. http://www.postgresql.org/docs/8.3/static/xaggr.html

the 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 need simple access to one of these arrays, which is returned. The following function adds an optional index parameter, which will return a nested pointer array that you provide, or, if you provide null, will display a complete set of "unset" 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) 

Answer 3

Warning: when using array_agg in postgres <9, the order may change PostgreSQL array_agg order If you plan to use an unsested array to search for argmax, this will corrupt your data.