Unnest array by one level

by

Function

To break out 1-dimensional arrays from n-dimensional arrays – representing leaves of the nested dimensions. (With n >= 1.)

PL/pgSQL

With a FOR loop looping through the array:

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
   FOREACH a_1d SLICE 1 IN ARRAY a LOOP
      RETURN NEXT;
   END LOOP;
END
$func$;

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

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:

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.

db<>fiddle here
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 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.

For very old versions

For Postgres versions < 8.4, array_agg() is not installed by default. 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}

More Related Contents:

  1. Unnest multiple arrays in parallel
  2. Return rows matching elements of input array in plpgsql function
  3. Loop over array dimension in plpgsql
  4. Iterating over integer[] in PL/pgSQL
  5. Can PostgreSQL index array columns?
  6. Can PostgreSQL have a uniqueness constraint on array elements?
  7. Compare arrays for equality, ignoring order of elements
  8. Using jsonb_set() for updating specific jsonb array value
  9. jsonb vs jsonb[] for multiple addresses for a customer
  10. Reading output of a command into an array in Bash
  11. Select the values of one property on all objects of an array in PowerShell
  12. Iterate over two arrays simultaneously in bash
  13. Creating Indicator Matrix
  14. How to initialize two-dimensional arrays in Fortran
  15. Printing array in Scala
  16. Array of Matrices in MATLAB
  17. Why have arrays in Go?
  18. Read tab-separated file line into array
  19. Is there a Perl shortcut to count the number of matches in a string?
  20. Pipe complete array-objects instead of array items one at a time?
  21. Assign to a bash array variable indirectly, by dynamically constructed variable name
  22. Unmarshal 2 different structs in a slice
  23. Swift: What’s the best way to pair up elements of an Array
  24. Why are Arrays invariant, but Lists covariant?
  25. Hashing a String to a Numeric Value in PostgreSQL
  26. How do I remove elements at a set of indices in a vector in MATLAB?
  27. How to forward functions with variadic parameters?
  28. How do I Merge two Arrays in VBA?
  29. jq: recursively merge objects and concatenate arrays
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment