[
https://issues.apache.org/jira/browse/FLINK-37267?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Martijn Visser reassigned FLINK-37267:
--------------------------------------
Assignee: Gustavo de Morais
> 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
> Assignee: 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)
