6

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" }
fgalan
  • 11,732
  • 9
  • 46
  • 89
sabrina2020
  • 2,102
  • 3
  • 25
  • 54

2 Answers2

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);
chridam
  • 100,957
  • 23
  • 236
  • 235
  • 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}` – sabrina2020 Sep 08 '15 at 15:21
  • 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")`. – chridam Sep 09 '15 at 06:39
  • 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 – sabrina2020 Sep 09 '15 at 07:13
  • 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? – chridam Sep 09 '15 at 07:17
  • I updated my question with some data and with the query executed on the Mongo shell, thanks. – sabrina2020 Sep 09 '15 at 07:28
0

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" } }

        }
      }
   ]
)