[
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.
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]
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:
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]
see also:
spark:[https://spark.apache.org/docs/latest/api/sql/index.html#slice]
> 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.
> 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]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
