Gustavo de Morais created FLINK-37267:
-----------------------------------------
Summary: 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
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.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
