[jira] [Assigned] (ASTERIXDB-2504) There is not a clean way of unnesting function return value

["Till (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/ASTERIXDB-2504?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Till reassigned ASTERIXDB-2504:
-------------------------------

    Assignee: Xikui Wang

> There is not a clean way of unnesting function return value
> -----------------------------------------------------------
>
>                 Key: ASTERIXDB-2504
>                 URL: https://issues.apache.org/jira/browse/ASTERIXDB-2504
>             Project: Apache AsterixDB
>          Issue Type: Bug
>            Reporter: Xikui Wang
>            Assignee: Xikui Wang
>            Priority: Major
>
> Given 
> {code}
> drop  dataverse feeds if exists;
> create  dataverse feeds;
> use feeds;
> create type TweetMessageType as
>  open {
>   id : bigint,
>   text : string
> };
> create type KeywordType as {
>     kid: int,
>     country: string,
>     word: string
> };
> create dataset Tweets(TweetMessageType) primary key id;
> create dataset Keywords(KeywordType) primary key kid;
> CREATE FUNCTION tweetsSafetyCheck(t) {
>   LET safetyCheckFlag = CASE EXISTS(SELECT VALUE k FROM Keywords k WHERE t.country = k.country AND contains(t.text, k.word))
>     WHEN true THEN "Red" ELSE "Green"
>     END
>   SELECT t.*, safetyCheckFlag
> };
> {code}
> if one want to query against the function result, he/she would have to unnest the result explicitly like this:
> {code}
> SELECT tweet.country Country, count(tweet) TweetNum FROM Tweets tweet
> LET enrichedTweet = tweetsSafetyCheck(tweet)*[0]*
> WHERE enrichedTweet.safetyCheckFlag = "Red"
> GROUP BY tweet.country;
> {code}
> It would be nice to allow writing such query without *[0]*, since one might expect the function returns a single object instead of a list which is generated by a subquery.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)