Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

postgresql - How to select 1d array from 2d array?

I have 2d array and want to select only first element of it, which is 1d array.
How do I do that?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

Response to your comment:
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 AS
$func$
SELECT $1[i]
FROM   generate_series(array_lower($1,1), array_upper($1,1)) i
$func$  LANGUAGE sql IMMUTABLE;

unnest() for 2-dimensional array:

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

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 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$  LANGUAGE sql IMMUTABLE;

SQL Fiddle.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...