Support Array function for multi-value columns

[xiangfu0]

Inspired by presto array functions(https://prestodb.io/docs/current/functions/array.html),
we can first support those transform functions on multi-value to 1 value result.

• array_average
• array_max
• array_min
• array_sum
• array_position
• cardinality
• contains
• element_at

[xiangfu0]

cc: @npawar @KKcorps

[xiangfu0]

Optimize query perf by existing multi-value aggregation functions capability.
#6127

[jpohanka]

@xiangfu0 Are there plans to implement the remaining functions, in particular, element_at ?

Currently, we need to use arraySlice for picking a single element from an array.

[walterddr]

do we want to support any boolean functions like

• MATCH_ANY
• MATCH_ALL
• MATCH_NONE
  (all of these are element-wise predicate evaluators)

[skjindal93]

Is there any plan to support ARRAY_AGG or ARRAY_JOIN aggregation function, which concatenates the values into an array?

[monicaluodialpad]

Do we have support on element_at already? Is this for returning the item at specific index?

[npawar]

We have arrayElementAt for primitive types https://github.com/apache/pinot/blob/master/pinot-common/src/main/java/org/apache/pinot/common/function/scalar/ArrayFunctions.java#L156

[monicaluodialpad]

@npawar Hi, thank you! I also find this but it seems that we don't have query support: https://github.com/apache/pinot/blob/master/pinot-common/src/main/java/org/apache/pinot/common/function/TransformFunctionType.java I didn't find any related function types.

[npawar]

@npawar Hi, thank you! I also find this but it seems that we don't have query support: https://github.com/apache/pinot/blob/master/pinot-common/src/main/java/org/apache/pinot/common/function/TransformFunctionType.java I didn't find any related function types.

In the link I shared, they've been added as ScalarFunctions. Scalar functions work for ingestion as well as query, so you should be able to use them in query. Are you not able to get them to work at query time?