Tested in Aqua Data Studio 14.0.3-14 Build #: 34647 on Ubuntu 12.04
It is link to the closed ticket 10444. Still one error present.
drop collection baseball
go
INSERT INTO baseball
VALUES (
{
"teamName": "Cubs-2",
"city": "Chicago",
"valuation": 10,
"managerName": { "first": "John", "last": "Zimmer" },
"colors": [ "blue", "gray" ],
"stats":
[
{
"year": 1904,
"wins": 100,
"mostRbis": 1000,
"grade": "A",
"battingAvg": 0.300
},
{
"year": 1987,
"wins":80,
"mostRbis":200,
"grade":"A",
"battingAvg":0.267
}
]
} )
go
INSERT INTO baseball
VALUES (
{
"teamName": "Cubs",
"city": "Luis",
"valuation": 100,
"managerName": { "first": "Dale", "last": "Sveum" },
"colors": [ "blue", "green","third" ],
"stats":
[
{
"year": 2008,
"wins": 60
},
{
"year": 1997,
"wins":100
}
]
} )
go
INSERT INTO baseball
VALUES (
{
"teamName": "Cubs-2",
"city": "Chicago",
"valuation": 10,
"managerName": { "first": "John", "last": "Belamy" },
"colors": [ "black", "grey" ],
"stats":
[
{
"year": 1008,
"wins": 0
},
{
"year": 1965,
"wins":2
}
]
} )
go
insert into baseball(valuation,city,mangerName,stats,colors) values (null,null,null,null,null)
go
Query with alias and without alias should produce same result set. But
select flatten_array stats from baseball group by stats
--outputs 7 columns. First column - "stats"
go
select flatten_array stats as sts from baseball group by stats
--outputs only 6 columns. Without "sts" as first column
go
while without group by both queries return 7 columns
select flatten_array stats from baseball
--outputs 7 columns. First column - "stats"
go
select flatten_array stats as sts from baseball
--outputs 7 columns. "sts" as first column
go
Verified in ADS 16.0.0-dev-34
select flatten_array stats from baseball group by stats
--outputs 7 columns. First column - "stats"
go