Hi Asma, There is some support for sort and aggregation in MongoDB but for sure there are small bugs here and there that need to be fixed. Your example seems similar to MongoAdapterTest#testDistinctCount [1] and indeed there seems to be a problem (CALCITE-2115 [2]) but nobody seems to be working on this at the moment. Check the remaining tests in MongoAdapterTest [3] to see queries that should work and if you find something that needs to be fixed please log a JIRA case.
Best, Stamatis [1] https://github.com/apache/calcite/blob/b49693d31964657bf5058bd9387e505992cebd51/mongodb/src/test/java/org/apache/calcite/adapter/mongodb/MongoAdapterTest.java#L557 [2] https://issues.apache.org/jira/browse/CALCITE-2115 [3] https://github.com/apache/calcite/blob/master/mongodb/src/test/java/org/apache/calcite/adapter/mongodb/MongoAdapterTest.java On Mon, Mar 1, 2021 at 11:01 PM asma zgolli <[email protected]> wrote: > hello , > > i m using apache calcite mongodb adapter but aggregations , group by and > sort are not working with mongodb adapter. > > select count(distinct _MAP['job']) from \"test\".\"banking\"" > > select * from \"test\".\"banking\" order by _MAP['job']" > > select * from \"test\".\"banking\" group by _MAP['job']" > > didn't work for me. > > > Exception in thread "main" java.sql.SQLException: Error while executing SQL > "select count(distinct _MAP['job']) from "test"."banking" ": While running > MongoDB query [{$project: {'$f0': '$job'}}, > > {$group: {_id: '$_0'}}, > > {$project: {_0: '$_id'}}, > > {$group: {_id: {}, 'EXPR$0': {$sum: {$cond: [ {$eq: ['_0', null]}, 0, > 1]}}}}] > > at org.apache.calcite.avatica.Helper.createException(Helper.java:56) > > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > > at org.apache.calcite.avatica.AvaticaStatement.executeInternal( > AvaticaStatement.java:163) > > at org.apache.calcite.avatica.AvaticaStatement.executeQuery( > AvaticaStatement.java:227) > > at > > at test.applications.calcite.test.test1querycalcite$delayedInit$body.apply( > test1querycalcite.scala:29) > > at scala.Function0$class.apply$mcV$sp(Function0.scala:34) > > at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12) > > at scala.App$$anonfun$main$1.apply(App.scala:76) > > at scala.App$$anonfun$main$1.apply(App.scala:76) > > at scala.collection.immutable.List.foreach(List.scala:381) > > at scala.collection.generic.TraversableForwarder$class.foreach( > TraversableForwarder.scala:35) > > at scala.App$class.main(App.scala:76) > > > > am i missing something? > > yours sincerely, > asma >
