-
1. Re: MongDB can't get group by to work.
rareddy Oct 25, 2013 5:39 AM (in response to ichanjasper)Can you try with a aggregate function like avg or sum in your select query? In the absence of aggregate it should work as DISTINCT per http://stackoverflow.com/questions/7343702/use-of-group-by-in-sql-only-with-aggregates
Also can you turn on DEBUG logging on "org.teiid.CONNECTOR" context and post the source query that is being executed as part of this? You can also post the query plan for this.
Thanks
Ramesh..
-
2. Re: MongDB can't get group by to work.
ichanjasper Oct 30, 2013 4:32 PM (in response to rareddy)Hi, I tried aggregate function with group by, but I still couldn't get it to work.
Original SQL:
select "grade", sum("score") as "sum_score" from "mongoDBDS"."grades" group by "grade" order by "grade"
Transform query:
SELECT grades.grade AS c_0, SUM(grades.score) AS c_1 FROM grades GROUP BY grades.grade ORDER BY c_0
$group:{ "_id" : "$grade" , "c_1" : { "$sum" : "$score"}}
$project:{ "c_0" : "$grade" , "c_1" : 1}
$sort:{ "c_0" : 1}
And it didn't work without aggregate function neither:
Original SQL:
select "grade" from "mongoDBDS"."grades" group by "grade" order by "grade"
Transform query:
SELECT grades.grade AS c_0 FROM grades GROUP BY grades.grade ORDER BY c_0
$group:{ "_id" : "$grade"}
$project:{ "c_0" : "$grade"}
$sort:{ "c_0" : 1}
Do you guys have any idea?
Thanks,
Ivan C.
-
3. Re: MongDB can't get group by to work.
rareddy Oct 31, 2013 9:16 AM (in response to ichanjasper)Do you see a exception? or get wrong results etc? what did not work?
Since you see the expressions that are being sent to MongoDB, if you execute then in MongoDB console do you get results, or you think the MongoDB expression generated by Teiid is in-correct?
-
4. Re: MongDB can't get group by to work.
ichanjasper Oct 31, 2013 8:11 PM (in response to rareddy)It got incorrect result.
Original SQL:
select "grade", sum("score") as "sum_score" from "mongoDBDS"."grades" group by "grade" order by "grade"
Transform query:
SELECT grades.grade AS c_0, SUM(grades.score) AS c_1 FROM grades GROUP BY grades.grade ORDER BY c_0
$group:{ "_id" : "$grade" , "c_1" : { "$sum" : "$score"}}
$project:{ "c_0" : "$grade" , "c_1" : 1}
$sort:{ "c_0" : 1}
Expected Result:
grade score
A 320
B 150
C 50
Actual Result: (WRONG)
grade score
520
It only returns SUM(score) and group by fields are ignore.
-
5. Re: MongDB can't get group by to work.
rareddy Oct 31, 2013 8:53 PM (in response to ichanjasper)Ivan,
Can you log a JIRA with VDB and some sample data.
Thanks
Ramesh..
-
6. Re: MongDB can't get group by to work.
ichanjasper Nov 1, 2013 7:28 PM (in response to rareddy)How do I print out the VDB from VDBMetaData?
-
7. Re: MongDB can't get group by to work.
rareddy Nov 1, 2013 8:10 PM (in response to ichanjasper)Take look at TestVDBMetadata