You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@asterixdb.apache.org by "Xikui Wang (JIRA)" <ji...@apache.org> on 2018/12/28 22:59:00 UTC
[jira] [Updated] (ASTERIXDB-2504) There is not a clean way of
unnesting function return value
[ https://issues.apache.org/jira/browse/ASTERIXDB-2504?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Xikui Wang updated ASTERIXDB-2504:
----------------------------------
Description:
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.
was:
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.
> 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
> 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)