MongoDb: Aggregation using Groovy language

Question

Iam using groovy scripting under SpagoBI. I want to use aggregation. I want for example to execute the following aggregation:

db.myCollection.aggregate(
   [
      {
        $group : {
           _id : { day: { $dayOfMonth: "$recvTime" } }

        }
      }
   ]
)

I tried:

DBObject projectFields = new BasicDBObject('$dayOfMonth',"recvTime");
DBObject project=new BasicDBObject('$project',projectFields)

DBObject groupFields = new BasicDBObject( "_id",project);

DBObject group = new BasicDBObject('$group', groupFields);

iterable = db.getCollection('myCollection').aggregate(group)  

I got this error:

An unexpected error occured while executing dataset: { "serverUsed" : "192.168.1.160:27017" , "errmsg" : "exception: invalid operator '$project'" , "code" : 15999 , "ok" : 0.0}

Any ideas?

Updates: the query executed in Mongo shell

db['cygnus_/kurapath_enocean_power_enocean'].aggregate(
...    [
...       {
...         $group : {
...            _id : { day: { $dayOfMonth: "$recvTime" } }
...         }
...       }
...    ]
... );
{ "_id" : { "day" : 9 } }
{ "_id" : { "day" : 8 } }
{ "_id" : { "day" : 7 } }
{ "_id" : { "day" : 4 } }
{ "_id" : { "day" : 3 } }

the data stored in mongo db:

db['cygnus_/kurapath_enocean_power_enocean'].find()
{ "_id" : ObjectId("55e81e9631d7791085668331"), "recvTime" : ISODate("2015-09-03T10:19:02Z"), "attrName" : "power", "attrType" : "string", "attrValue" : "2085.0" }
{ "_id" : ObjectId("55e81e9631d7791085668332"), "recvTime" : ISODate("2015-09-03T10:19:02Z"), "attrName" : "power", "attrType" : "string", "attrValue" : "2085.0" }
{ "_id" : ObjectId("55e81e9831d7791085668333"), "recvTime" : ISODate("2015-09-03T10:19:04Z"), "attrName" : "power", "attrType" : "string", "attrValue" : "2077.0" }

Coriecorilla

Answer 1

From the error, the aggregation is not expecting the $project operator so you should change the projectFields and project variables to show the actual pipeline expressions i.e.

DBObject dateFields = new BasicDBObject("$dayOfMonth", "$recvTime");
DBObject dateObject = new BasicDBObject("day", dateFields);

DBObject groupFields = new BasicDBObject( "_id", dateObject);
DBObject group = new BasicDBObject('$group', groupFields);

iterable = db.getCollection('myCollection').aggregate(group);

Birdman

I tried your code but I got a new error :( : An unexpected error occured while executing dataset: No such property: dayOfMonth for class: Script119 when I tried DBObject dateFields = new BasicDBObject('$dayOfMonth', "recvTime"); I got ` An unexpected error occured while executing dataset: { "serverUsed" : "192.168.1.160:27017" , "errmsg" : "exception: can't convert from BSON type String to Date" , "code" : 16006 , "ok" : 0.0}` – Coriecorilla

You forgot to include the $ in your "recvTime" field, so change your line DBObject dateFields = new BasicDBObject('$dayOfMonth', "recvTime") to DBObject dateFields = new BasicDBObject('$dayOfMonth', "$recvTime"). – Birdman

I updated the code to DBObject dateFields = new BasicDBObject('$dayOfMonth', "$recvTime") but I got this error An unexpected error occured while executing dataset: No such property: recvTime for class, if I updated to DBObject dateFields = new BasicDBObject('$dayOfMonth', '$recvTime'); I got an empty result set – Coriecorilla

Looks like the latter (with single quotes) works, can't get much inference from the empty result set without some sample data. Is the recvTime field of ISODate type? – Birdman

I updated my question with some data and with the query executed on the Mongo shell, thanks. – Coriecorilla

Answer 2

In SpagoBI you have 2 ways to create a dataset of type MongoDB: 1) use a dataset of type Query and use JS language 2) use a dataset of type java class and write your code in java For solution 1 you should create a dataset of type query, select a datasource MongoDB and write on the field QUERY the query in js following the specification written here (http://wiki.spagobi.org/xwiki/bin/view/spagobi_server/data_set#HQueryDataSet28Mongo29)

The button “Edit script” is used to modify the query string and you can find some tip in the wiki. So if the query is SQL you can use a js o groovy script to change the query string (for example apply some logic to create parameters or table names)

You can express your query in this way:

Var query = db.myCollection.aggregate(
   [
      {
        $group : {
           _id : { day: { $dayOfMonth: "$recvTime" } }

        }
      }
   ]
)

Camarena