get last element of array of unknown size

user_2573061 0
Viewed 3417

In Snowflake, I can get the first element of an array without knowing its length:

with foo as (
  select array_construct('duck','duck','goose') as a
)
select a[0] from foo -- returns 'duck'

But to get the last element I can't use the "pythonic" a[-1], because select a[-1] from foo returns:

Invalid extraction path '-1': array index -1 is off limits; must be between 0 and 2,147,483,647.

Interestingly, I can use array_slice() to get the next-to-last element:

select array_slice(a,-2,-1) from foo -- returns 'duck'

but not the last element, since "the output includes elements up to, but not including the element specfied [sic] by the parameter" (doc).

How do I get the 'goose'?

snowflake-cloud-data-platform sql
4 Answers

We could use the a[len(a)-1] approach:

with foo as (
  select array_construct('duck','duck','goose') as a
)
select a[array_size(a)-1]::varchar from foo -- returns 'goose'
user_25730610

This should work.

CREATE OR REPLACE FUNCTION ARRAY_ELEMENT(a ARRAY,b double)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS '
    if (B < 0) 
    {
        element = A[A.length + B];
    }
    else
    {
        element = A[B];
    }
    return element
';

select ARRAY_ELEMENT(array_construct(0,1,2,3,4,5,6),-1); //returns 6
select ARRAY_ELEMENT(array_construct(0,1,2,3,4,5,6),-2); //returns 5

user_57054740

Similar to the answer from C8H10N4O2 but without the CTE:

select  array_construct('duck','duck','goose') AS birds ,
birds[array_size(birds)-1]::STRING AS lastbird
user_11692900

If you have a known maximum length of the array you can do this:

select array_slice([1,2,3,4,5,6,7], -1, 9999)

where 9999 is bigger than the maximum length of the array

user_194027180
Related