Presto: Removing last element from variable length array

I have arrays getting past through of variable lengths. I need to remove the last element. Example:

Array[1,2,3] -> Array[1,2]

Array[5,2,1,4] -> Array[5,2,1]

I have attempted to set up slice(Array[],-1) to start from the back but I don't know the length and am trying to avoid nesting the query further as it is already messy.

I've read through these options but don't find an explanation that I think fits: https://prestodb.github.io/docs/current/functions/array.html

1 answer

  • answered 2019-10-02 19:18 Piotr Findeisen

    Currently, the only way to have create an array without last element is using slice:

    -- does not work for empty arrays
    slice(a, 1, cardinality(a) - 1)
    

    To take care of empty arrays you can wrap this in try:

    -- returns NULL for empty array
    try(slice(a, 1, cardinality(a) - 1))
    

    The slice function supports negative index for specifying start position counting from the end of the array, but the sliced length must be given explicitly. Please create a feature request.