[
https://issues.apache.org/jira/browse/FLINK-37267?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Gustavo de Morais updated FLINK-37267:
--------------------------------------
Description:
The SQL standard specifies a WITH ORDINALITY clause that can be appended to any
UNNEST function call, which returns a new row for each element and its position
in the data structure being unnested.
Examples:
{code:java}
– Returns a new row for each element in a constant array and its position in
the array
SELECT *
FROM (VALUES('order_1'))
CROSS JOIN UNNEST(ARRAY["shirt", "pants", "hat"])
WITH ORDINALITY AS t(product_name, index)
id product_name index
======= ============ =====
order_1 shirt 1
order_1 pants 2
order_1 hat 3{code}
Or
{code:java}
– Returns a new row for each element and its position in the array assuming a
Orders table with an array column `product_names`
SELECT order_id, product_name, product_index
FROM Orders
CROSS JOIN UNNEST(product_names)
WITH ORDINALITY AS t(product_name, product_index){code}
A unnest with ordinality will return each element and the position of the
element in the data structure, 1-indexed. The order of the elements for arrays
and multisets is guaranteed. Since maps are unordered, the order of the
elements is not guaranteed.
was:
The SQL standard specifies a WITH ORDINALITY clause that can be appended to any
UNNEST function call, which returns a new row for each element and its position
in the data structure being unnested.
Examples:
```sql
– Returns a new row for each element in a constant array and its position in
the array
SELECT *
FROM (VALUES('order_1'))
CROSS JOIN UNNEST(ARRAY["shirt", "pants", "hat"])
WITH ORDINALITY AS t(product_name, index)
id product_name index
======= ============ =====
order_1 shirt 1
order_1. pants 2
order_1. hat 3
– Returns a new row for each element and its position in the array assuming a
Orders table with an array column `product_names`
SELECT order_id, product_name, product_index
FROM Orders
CROSS JOIN UNNEST(product_names)
WITH ORDINALITY AS t(product_name, product_index)
```
A unnest with ordinality will return each element and the position of the
element in the data structure, 1-indexed. The order of the elements for arrays
and multisets is guaranteed. Since maps are unordered, the order of the
elements is not guaranteed.
> Support WITH ORDINALITY for UNNEST
> ----------------------------------
>
> Key: FLINK-37267
> URL: https://issues.apache.org/jira/browse/FLINK-37267
> Project: Flink
> Issue Type: New Feature
> Components: Table SQL / API
> Reporter: Gustavo de Morais
> Priority: Major
> Labels: pull-request-available
>
> The SQL standard specifies a WITH ORDINALITY clause that can be appended to
> any UNNEST function call, which returns a new row for each element and its
> position in the data structure being unnested.
> Examples:
> {code:java}
> – Returns a new row for each element in a constant array and its position in
> the array
> SELECT *
> FROM (VALUES('order_1'))
> CROSS JOIN UNNEST(ARRAY["shirt", "pants", "hat"])
> WITH ORDINALITY AS t(product_name, index)
> id product_name index
> ======= ============ =====
> order_1 shirt 1
> order_1 pants 2
> order_1 hat 3{code}
> Or
> {code:java}
> – Returns a new row for each element and its position in the array assuming a
> Orders table with an array column `product_names`
> SELECT order_id, product_name, product_index
> FROM Orders
> CROSS JOIN UNNEST(product_names)
> WITH ORDINALITY AS t(product_name, product_index){code}
> A unnest with ordinality will return each element and the position of the
> element in the data structure, 1-indexed. The order of the elements for
> arrays and multisets is guaranteed. Since maps are unordered, the order of
> the elements is not guaranteed.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
