Problem in Aggregation.group AggregationOperation on multiple sums [DATAMONGO-1516]

[spring-projects-issues]

Davide Fabbri opened DATAMONGO-1516 and commented

Hi,
I'm trying to execute an AggregationOperation on DeviceDataCount equal to the following query:
DESIRED QUERY

db.device_data_count.aggregate(
	
//	{"$match":  { deviceId : { "$in" : ["C3786"] }}}
//	,
	{ "$unwind" : "$countSet"}
	,
//	{"$match":  { "countSet.detValTypeId": { "$in": ["AIRTEMP"] }}}
//	,
	{ "$project" : {deviceId: 1, detValTypeId: "$countSet.detValTypeId", count: "$countSet.count", totalSet: 1}}
	,
	{ "$unwind" : "$totalSet"}
	,
//	{"$match":  { "totalSet.detValTypeId": { "$in": ["AIRTEMP"] } }}
//	,
	{ "$project" : {deviceId: 1, detValTypeId: 1, count: 1, detValTypeId2: "$totalSet.detValTypeId", total: "$totalSet.total"}}
	,
	{ "$project" : {deviceId: 1, detValTypeId: 1, count: 1, total: 1, isEqual : { "$eq" : ["$detValTypeId", "$detValTypeId2"]}}}
	,
	{"$match": {isEqual: true}}
	,
	{ "$group" : { _id : "$detValTypeId",  count : { $sum : "$count"}, total : {$sum : "$total"} , deviceIds : { $addToSet : "$deviceId"}}}

	)

I commented optional filter on fields, this query is obviously working.

In my data repository as you can see in the file attached, I tryed to use Spring Group Operation:
SPRING PROBLEM

// NOT WORKING SPRING GroupOperation
		operations.add(Aggregation.group("detValTypeId").sum("total").as("total").sum("count").as("count").addToSet("deviceId").as("deviceIds"));

but it fails in producing "total" sum.
RESULTING QUERY

db.device_data_count.aggregate(
{ "$unwind" : "$countSet"}
,
{ "$project" : { "deviceId" : 1 , "totalSet" : 1 , "detValTypeId" : "$countSet.detValTypeId" , "count" : "$countSet.count"}}
,
{ "$unwind" : "$totalSet"}
,
{ "$project" : { "deviceId" : 1 , "detValTypeId" : 1 , "count" : 1 , "detValTypeId2" : "$totalSet.detValTypeId" , "total" : "$totalSet.total"}}
,
{ "$project" : { "deviceId" : 1 , "detValTypeId" : 1 , "count" : 1 , "total" : 1 , "isEqual" : { "$eq" : [ "$detValTypeId" , "$detValTypeId2"]}}}
,
{ "$match" : { "isEqual" : true}}
,
{ "$group" : { "_id" : "$detValTypeId" , "count" : { "$sum" : "$count"} , "total" : { "$sum" : "$totalSet.total"} , "deviceIds" : { "$addToSet" : "$deviceId"}}}
)

RESULT

{ 
    "_id" : "PRESSURE", 
    "count" : NumberLong(4159), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "C3786"
    ]
}
{ 
    "_id" : "AIRTEMP", 
    "count" : NumberLong(14628), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "C3786", 
        "74617", 
        "745E6"
    ]
}
{ 
    "_id" : "RELHUM", 
    "count" : NumberLong(13379), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "C3786", 
        "74617", 
        "745E6"
    ]
}
{ 
    "_id" : "AMBLIGHT", 
    "count" : NumberLong(3627), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "C3786"
    ]
}
{ 
    "_id" : "PROXIMIT", 
    "count" : NumberLong(3627), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "C3786"
    ]
}
{ 
    "_id" : "SOILPOT", 
    "count" : NumberLong(2228), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "74617"
    ]
}
{ 
    "_id" : "BATTERY", 
    "count" : NumberLong(10469), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "74617", 
        "745E6"
    ]
}
{ 
    "_id" : "PRESSWIT", 
    "count" : NumberLong(6755), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "745E6"
    ]
}

Using a CustomAggregrationClass:

	private class CustomAggregationOperation implements AggregationOperation {
		private DBObject operation;

		public CustomAggregationOperation(DBObject operation) {
			this.operation = operation;
		}

		@Override
		public DBObject toDBObject(AggregationOperationContext context) {
			return context.getMappedObject(operation);
		}
	}

and classic MongoDB BasicDbObjects it works:

operations.add(new CustomAggregationOperation(new BasicDBObject("$group",
          new BasicDBObject("_id", "$detValTypeId").append("count", new BasicDBObject("$sum", "$count")).append("total", new BasicDBObject("$sum", "$total")).append("deviceIds", new BasicDBObject("$addToSet", "$deviceId")))));

RESULT

{ 
    "_id" : "PRESSURE", 
    "count" : NumberLong(4159), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "C3786"
    ]
}
{ 
    "_id" : "AIRTEMP", 
    "count" : NumberLong(14628), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "C3786", 
        "74617", 
        "745E6"
    ]
}
{ 
    "_id" : "RELHUM", 
    "count" : NumberLong(13379), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "C3786", 
        "74617", 
        "745E6"
    ]
}
{ 
    "_id" : "AMBLIGHT", 
    "count" : NumberLong(3627), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "C3786"
    ]
}
{ 
    "_id" : "PROXIMIT", 
    "count" : NumberLong(3627), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "C3786"
    ]
}
{ 
    "_id" : "SOILPOT", 
    "count" : NumberLong(2228), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "74617"
    ]
}
{ 
    "_id" : "BATTERY", 
    "count" : NumberLong(10469), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "74617", 
        "745E6"
    ]
}
{ 
    "_id" : "PRESSWIT", 
    "count" : NumberLong(6755), 
    "total" : NumberInt(0), 
    "deviceIds" : [
        "745E6"
    ]
}

Thanks,
Davide Fabbri

---

Affects: 1.9.4 (Hopper SR4)

Attachments:

• DetValTypeCounter.java (747 bytes)
• DeviceDataCount.java (2.15 kB)
• DeviceDataCountInfo.java (384 bytes)
• DeviceDataTotalInfo.java (394 bytes)
• DevicesDataCountRepository.java (7.93 kB)