[CALCITE-5751] Add ARRAY_APPEND, ARRAY_POSITION, ARRAY_REMOVE ARRAY_PREPEND for Spark dialect

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.35.0
• Fix Version/s: 1.35.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Jacky Lau
• Reporter: Jacky Lau
• Created: 05/Jun/23 13:09
• Updated: 27/Jul/23 10:22
• Resolved: 21/Jun/23 13:06

Description

array_append

array_append(array, element) - Add the element at the end of the array passed as first argument. Type of element should be similar to type of the elements of the array. Null element is also appended into the array. But if the array passed, is NULL output is NULL

Examples:

 

*> SELECT array_append(array('b', 'd', 'c', 'a'), 'd');
["b","d","c","a","d"]
> SELECT array_append(array(1, 2, 3, null), null);
[1,2,3,null,null]
> SELECT array_append(CAST(null as Array<Int>), 2);
NULL*

 

array_prepend is not in the docs, but in the code

https://issues.apache.org/jira/browse/SPARK-41233

usage = """ _FUNC_(array, element) - Add the element at the beginning of the array passed as first argument. Type of element should be the same as the type of the elements of the array. Null element is also prepended to the array. But if the array passed is NULL output is NULL """, examples = """ Examples: > SELECT _FUNC_(array('b', 'd', 'c', 'a'), 'd'); ["d","b","d","c","a"] > SELECT _FUNC_(array(1, 2, 3, null), null); [null,1,2,3,null] > SELECT _FUNC_(CAST(null as Array<Int>), 2); NULL

case class ArrayPrepend(left: Expression, right: Expression) extends RuntimeReplaceable 

array_position

array_position(array, element) - Returns the (1-based) index of the first element of the array as long.

Examples:

 

> SELECT array_position(array(3, 2, 1), 1);
 3 

 

array_remove

array_remove(array, element) - Remove all elements that equal to element from array.

Examples:

 

> SELECT array_remove(array(1, 2, 3, null, 3), 3);
 [1,2,null] 

 

 

Issue Links

• links to: GitHub Pull Request #3260