I have a presto array and I need to filter out odd indices no matter what the values in an array are.
Array = ['no', 'matter', 'what', 'is, 'here']
Desired result = ['matter', 'is']
I've tried quite a lot of different variations of sequence(2, cardinality(Array), 2) but nothing seem to have worked.
I ended up zipping the array with an index array that I created
zip(ar, sequence(1, cardinality(ar), 1))
then I filtered x[2] on x -> x[2]%2=0 and selected only x -> x[1] with transform.
Some other suggestion in a different place was unnesting with ordinality, filtering on ordinality column and then aggregating back.
Another approach - generate sequence of only needed indexes (for even ones - start with 2 and set step to 2) and use transform on it:
WITH dataset(arr) AS ( values (array[1,2,3,4,5]) )
SELECT transform(sequence(2, cardinality(arr), 2), i -> arr[i])
FROM dataset
Output:
| _col0 |
|---|
| [2, 4] |