[jira] [Comment Edited] (FLINK-19196) FlinkSQL aggregation function to aggregate array typed column to Map/Multiset

["Jark Wu (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/FLINK-19196?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17195836#comment-17195836 ] 

Jark Wu edited comment on FLINK-19196 at 9/15/20, 3:03 AM:
-----------------------------------------------------------

Hi, Flink doesn't provide such built-in function yet. You can implement a UDF for this purpose. As a workaround, you can use 2 separate UDF to achieve this.


{code:java}
SELECT userID, COLLECT(type)
FROM (
  SELECT userID, type
  FROM src_table CROSS JOIN UNNEST(arr_types) AS t (type)
) GROUP BY userID
{code}



was (Author: jark):
Hi, Flink doesn't provide such built-in function yet. You can implement a UDF for this purpose. As a workaround, you can use 2 separate UDF to achieve this.


{code:java}
SELECT userID, COLLECT(type)
FROM (
  SELECT userID, type
  FROM Orders CROSS JOIN UNNEST(arr_types) AS t (type)
) GROUP BY userID
{code}


> FlinkSQL aggregation function to aggregate array typed column to Map/Multiset
> -----------------------------------------------------------------------------
>
>                 Key: FLINK-19196
>                 URL: https://issues.apache.org/jira/browse/FLINK-19196
>             Project: Flink
>          Issue Type: New Feature
>          Components: Table SQL / API
>    Affects Versions: 1.11.1
>            Reporter: sam lin
>            Priority: Major
>
> Hi,
>  
> For FlinkSQL, how do I aggregate an array typed column to a Map/Multiset type? e.g. Given the source events:
> userID, arr_types
> 1, [a, b]
> 2, [a]
> 1, [b, c]
>  
> ```
> SELECT userID, collect_arr(arr_types) FROM src_table GROUP BY userID
> ```
> Using the above SQL could produce results like:
> 1, \{a: 1, b: 2, c:1}
> 2, \{a: 1}
> My question is do we have existing UDF to do that?  If not, what is the recommended way to achieve this?  I'm happy to write a UDF if you could provide some code pointers.  Thanks in advance!



--
This message was sent by Atlassian Jira
(v8.3.4#803005)