[
https://issues.apache.org/jira/browse/FLINK-32260?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sergey Nuyanzin resolved FLINK-32260.
-------------------------------------
Resolution: Fixed
Merged to master as
[e6e0f47bf3e7f7541da9e7be009cc6dcf4d86129|https://github.com/apache/flink/commit/e6e0f47bf3e7f7541da9e7be009cc6dcf4d86129]
> Add ARRAY_SLICE support in SQL & Table API
> ------------------------------------------
>
> Key: FLINK-32260
> URL: https://issues.apache.org/jira/browse/FLINK-32260
> Project: Flink
> Issue Type: Improvement
> Components: Table SQL / Planner
> Affects Versions: 1.18.0
> Reporter: Bonnie Varghese
> Assignee: Hanyu Zheng
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.18.0
>
>
> Implement the array_slice function to extract a subset of elements from an
> array.
> This function returns a subset of the input array, starting from
> 'start_offset' and ending at 'end_offset'. These offsets are 1-based and can
> be positive (counted from the start of the array) or negative (counted from
> the end of the array).
> Initially, the function checks if 'start_offset' or 'end_offset' are 0. If
> so, they are treated as starting from the beginning of the input array.
> Next, the function checks if 'start_offset' is after 'end_offset' or if the
> input array is empty. In such cases, it will return an empty array.
> For the remaining cases, the function proceeds as follows: if either
> 'start_offset' or 'end_offset' exceed the array bounds, they are adjusted to
> the size of the array. Conversely, if the absolute value of a negative
> 'start_offset' or 'end_offset' is greater than the size of the array, the
> corresponding offset is reset to the beginning of the array, which is 1.
> The function will return null if any input is null.
> Syntax:
>
> code
> {code:java}
> ARRAY_SLICE(array, start_offset, end_offset){code}
> {{ }}
> Arguments:
> array: The array that contains the elements you want to slice.
> start_offset: The inclusive starting offset.
> end_offset: The inclusive ending offset.
> An offset can be positive or negative. A positive offset starts from the
> beginning of the input array and is 1-based. A negative offset starts from
> the end of the input array. Out-of-bounds offsets are supported. if
> start_offset == 0 or end_offset == 0, we treat them start from the beginning
> of the input array.
> Returns:
> The input array can contain NULL elements. NULL elements are included in the
> resulting array.
> Returns NULL if array, start_offset, or end_offset is NULL.
> Returns an empty array if array is empty.
> Returns an empty array if the position of the start_offset in the array is
> after the position of the end_offset.
> Examples:
>
> {code:java}
> SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 1, 3)
> Output: [a, b, c]
> SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -1, -3)
> Output: []
> SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -3, -1)
> Output[c, d, e]
> SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 3, 3)
> Output[c]
> SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 1, 30)
> Output[a, b,c,d,e]
> SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 1, -30)
> Output[]
> SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -30, 30)
> Output[a, b, c, d, e]
> SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -30, -5)
> Output[a]
> SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 5, 30)
> Output[e]
>
> SELECT ARRAY_SLICE(['a', 'b', NULL, 'd', 'e'], 1, 3)
> Output[a, b, null]
> SELECT ARRAY_SLICE(['a', 'b', NULL, 'd', 'e'], 0, 0)
> Output[a]
> {code}
>
> see also:
> spark: [https://spark.apache.org/docs/latest/api/sql/index.html#slice]
> google cloud:
> [https://cloud.google.com/spanner/docs/reference/standard-sql/array_functions#array_slice]
> ClickHouse:
> [https://clickhouse.com/docs/en/sql-reference/functions/array-functions#arrayslice]
> DockDb: [https://duckdb.org/docs/sql/functions/nested#list-functions]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
