Tested Aqua Data Studio Aqua Data Studio 14.0.0-beta-74 Build #: 33340 on Ubuntu 12.04( Mongo DB 2.4.3) and Windows XP ( Mongo DB 2.0.9).
1. Alias in GROUP BY or MIN(), MAX() double result set and change output format:
Normaly:
select flatten_array stats from baseball group by stats
go
-- returns 2 rows with many columns
--stats.year stats.wins stats.mostRbis stats.grade stats.battingAvg
--1904 100 1000 A 0.3
--1987 80 200 A 0.267
But with alias:
select flatten_array stats as sts from baseball group by stats
go
--returns 4 rows instead 2 and only 1 column instead many
--sts
--{ "year" : 1904 , "wins" : 100 , "mostRbis" : 1000 , "grade" : "A" , "battingAvg" : 0.3}
--{ "year" : 1904 , "wins" : 100 , "mostRbis" : 1000 , "grade" : "A" , "battingAvg" : 0.3}
--{ "year" : 1987 , "wins" : 80 , "mostRbis" : 200 , "grade" : "A" , "battingAvg" : 0.267}
--{ "year" : 1987 , "wins" : 80 , "mostRbis" : 200 , "grade" : "A" , "battingAvg" : 0.267}
Same problem with MIN( ) and MAX( ) functions:
Without GROUP BY 2 rows returns with 1 column( should be many columns)
select flatten_array min(stats) from baseball
go
--min(stats)
--{ "year" : 1904 , "wins" : 100 , "mostRbis" : 1000 , "grade" : "A" , "battingAvg" : 0.3}
--{ "year" : 1987 , "wins" : 80 , "mostRbis" : 200 , "grade" : "A" , "battingAvg" : 0.267}
GROUP BY doubles result set:
select flatten_array min(stats) from baseball group by stats
go
--returns 4 rows instead 2 and only 1 column instead many
--min(stats)
--{ "year" : 1904 , "wins" : 100 , "mostRbis" : 1000 , "grade" : "A" , "battingAvg" : 0.3}
--{ "year" : 1987 , "wins" : 80 , "mostRbis" : 200 , "grade" : "A" , "battingAvg" : 0.267}
--{ "year" : 1904 , "wins" : 100 , "mostRbis" : 1000 , "grade" : "A" , "battingAvg" : 0.3}
--{ "year" : 1987 , "wins" : 80 , "mostRbis" : 200 , "grade" : "A" , "battingAvg" : 0.267}
2. Alias changes output format in FLATTEN_ARRAY (without GROUP BY)
select flatten_array stats from baseball
go
-- returns 2 rows with many columns
--stats.year stats.wins stats.mostRbis stats.grade stats.battingAvg
--1904 100 1000 A 0.3
--1987 80 200 A 0.267
But with using alias only 1 column in output:
select flatten_array stats as sts from baseball
go
--sts
--{ "year" : 1904 , "wins" : 100 , "mostRbis" : 1000 , "grade" : "A" , "battingAvg" : 0.3}
--{ "year" : 1987 , "wins" : 80 , "mostRbis" : 200 , "grade" : "A" , "battingAvg" : 0.267}
