I have a 2d array and want to select only the 1st 1d array from it.
How do I do that?
2 Answers
To get the first slice of an array:
SELECT my_arr[1:1];
The resulting array has the same array dimensions as the input.
Details in my previous answer here:
To flatten the result:
SELECT ARRAY(SELECT unnest(my_arr[1:1]));
Or cleaner:
SELECT ARRAY(SELECT * FROM unnest(my_arr)[1:1]));
Examples
SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1:1];
Result:
{{1,2,3}} -- 2D array
Or:
SELECT ARRAY(
SELECT unnest((ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1:1])
);
Result:
{1,2,3} -- 1D array
Emulate unnest() in Postgres 8.3
The Wiki page you are linking to was a bit misleading. I updated it with code for 2-dimensional arrays.
unnest() for 1-dimensional array:
CREATE OR REPLACE FUNCTION unnest_1d(anyarray)
RETURNS SETOF anyelement
LANGUAGE sql IMMUTABLE AS
$func$
SELECT $1[i]
FROM generate_series(array_lower($1,1), array_upper($1,1)) i
$func$;
unnest() for 2-dimensional array:
CREATE OR REPLACE FUNCTION unnest_2d(anyarray)
RETURNS SETOF anyelement
LANGUAGE sql IMMUTABLE AS
$func$
SELECT $1[d1][d2]
FROM generate_series(array_lower($1,1), array_upper($1,1)) d1
, generate_series(array_lower($1,2), array_upper($1,2)) d2
$func$;
The aggregate function array_agg() is not installed by default in Postgres 8.3:
CREATE AGGREGATE array_agg(anyelement) (
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
Unnest 2d array to 1d arrays:
CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray)
RETURNS SETOF anyarray
LANGUAGE sql IMMUTABLE AS
$func$
SELECT array_agg($1[d1][d2])
FROM generate_series(array_lower($1,1), array_upper($1,1)) d1
, generate_series(array_lower($1,2), array_upper($1,2)) d2
GROUP BY d1
ORDER BY d1
$func$;
answered Sep 23, 2014 at 14:18
Erwin Brandstetter
669k160 gold badges1.2k silver badges1.3k bronze badges
3 Comments
thecoparyew
I wanted 1d array because version of postgres I work on is pretty old (8.3) and there aren't unnest function. So I created function by this link wiki.postgresql.org/wiki/Array_Unnest but it is not working for 2d array. With 1d array it works fine.
Erwin Brandstetter
@thecoparyew: That information should be in your question to begin with. I updated the misleading Wiki page and added some more to the answer. The other option is to upgrade to a current version of Postgres. Version 8.3 has reached EOL in Feb. 2013.
Peter Krauss
To be didactic, more one information to reader: how to select de second or Nth item (line of the matrix)? Answer: by slices
x[n:n]... but with the same ugly problem, that the slice od 2D is not 1D, is also 2D.You can get the 1st 1D array from a 2D array with these ways below.
SELECT (ARRAY[['a','b','c'],['d','e','f']])[1:1]; -- {{a,b,c}}
SELECT (ARRAY[['a','b','c'],['d','e','f']])[1:1][:]; -- {{a,b,c}}
SELECT (ARRAY[['a','b','c'],['d','e','f']])[1:1][1:3]; -- {{a,b,c}}
SELECT (ARRAY[['a','b','c'],['d','e','f']])[1][:]; -- {{a,b,c}} -- This is tricky
SELECT (ARRAY[['a','b','c'],['d','e','f']])[1][1:3]; -- {{a,b,c}} -- This is tricky
SELECT (ARRAY[['a','b','c'],['d','e','f']])[1]; -- NULL
answered Feb 5, 2024 at 3:42
Super Kai - Kazuya Ito
42.8k23 gold badges258 silver badges257 bronze badges
SELECT (ARRAY [ ARRAY[1,2], ARRAY[3,4] ])[1:1];