[
https://issues.apache.org/jira/browse/FLINK-32260?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hanyu Zheng updated FLINK-32260:
--------------------------------
Description:
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]
was:
Implement the array_slice function to extract a subset of elements from an
array.
Returns a subset of input array between start_offset and end_offset. The
offsets are 1-based and can be positive (from start) or negative (from end). If
start_offset or end_offset exceed array bounds, they're set to array size. if
start_ offset or end_offset is smaller than 1, they are set to 1. Returns null
if any input is null, and return an empty array if start_offset is after
end_offset or if the input array is empty.
if start_offset == 0 or end_offset == 0, we treat them start from the beginning
of the input array.
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]
> 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)
