0

After reading this I wrote a naive attempt to produce this

col1
---------
1
4
7

from this

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

This works

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

But I in my case, I don't know the length of the outer array.

So is there a way to hack together the slice "string" to take into account this variability?

Here was my attempt. I know, it's a bit funny

_ids := _ids_2D[('1:' || array_length(_ids_2D, 1)::text)::int][1:1];

As you can see, I just want to create the effect of [1:n]. Obviously '1:3' ain't going to parse nicely into what the array slice needs.

I could obviously use something like the unnest_2d_1d Erwin mentions in the answer linked above, but hoping for something more elegant.

Community
  • 1
  • 1
poshest
  • 4,157
  • 2
  • 26
  • 37
  • Do you want an array with the first element of all nested (2nd level) of an array (1st level)? – lnrdo Feb 23 '15 at 16:10
  • I am not sure if the array_length would not work, because the 1:n is a string anyway. Need to check. – lnrdo Feb 24 '15 at 09:31

1 Answers1

5

If you are trying to get the first element of all nested (2nd dimension) arrays inside an array (1st dimension) then you may use 

array_upper(anyarray, 1)

to get all elements of a specific dimension

anyarray[1:array_upper(anyarray, 1)][<dimension num>:<dimension num>]

e.g, to get all elements of the first dimension

anyarray[1:array_upper(anyarray, 1)][1:1]

as in the code above. Please refer to PostgreSQL manual section on Arrays for more information.

gilad905
  • 2,842
  • 2
  • 16
  • 23
lnrdo
  • 396
  • 1
  • 13
  • Great stuff Inrdo! `array_length` that I was using would also work. But it was trying to make a string first that caused my problem. – poshest Feb 23 '15 at 21:08