Multiple $group in Mongodb Aggregation with java

Question

Here is my Query,

db.product.aggregate([
{ $match : {categoryID : 4 } },
{ "$group" : { "_id" : { "productID": "$productID", 
                         "articleID": "$articleID", "colour":"$colour",
                         "set&size": { "sku" : "$skuID", "size" : "$size" },  
                        }, 
              }
},
{ "$group" : { "_id" : { "productID": "$_id.productID", "colour":"$_id.colour" }, 

   "size": { "$addToSet" : { "sku" : "$_id.set&size.sku", 
                                           "size" : "$_id.set&size.size" }
                         },   
 }
},
{"$project":{
     "_id":0,
     "productID":  "$_id.productID",
     "colour":"$_id.colour",
     "size":"$size",
     }
   },

   ]);

By executing this query on mongo shell i get perfect output.

output

{
"_id": {
    "productID": "PRD1523",
    "colour": "GREEN"
},
"size": [
    {
        "sku": "ALT50095",
        "size": "S"
    },
    {
        "sku": "ALT50096",
        "size": "XL"
    }
]
}
{
"_id": {
    "productID": "PRD1523",
    "colour": "RED"
},
"size": [
    {
        "sku": "ALT50094",
        "size": "M"
    },
    {
        "sku": "ALT50093",
        "size": "S"
    }
]
}

but when with my java code it gives exception.

Here is java code for above query,

DBCollection table = mongoTemplate.getCollection(collection_name);

    BasicDBObject matchTopics = new BasicDBObject();
    matchTopics.put("categoryID", 4);

    DBObject groupSameIdEntities = new BasicDBObject("_id", new BasicDBObject("productID", "$productID")
            .append("articleID", "$articleID").append("colour", "$colour")
            .append("set&size", new BasicDBObject("sku", "$skuID").append("size", "$size")));

DBObject secondGroup = new BasicDBObject("_id", new BasicDBObject("colour", "$_id.colour").append("productID",
            "$_id.productID").append(
            "size",
            new BasicDBObject("$addToSet", new BasicDBObject("sku", "$_id.set&size.sku").append("size",
                    "$_id.set&size.size"))));

AggregationOutput output = table.aggregate(new BasicDBObject("$match", matchTopics), new BasicDBObject(
            "$group", groupSameIdEntities), new BasicDBObject("$group", secondGroup));

Exception

HTTP Status 500 - Request processing failed; nested exception is com.mongodb.CommandFailureException: { "serverUsed" : "127.0.0.1:27017" , "errmsg" : "exception: invalid operator '$addToSet'" , "code" : 15999 , "ok" : 0.0}

I can't figure out how to solve this error.

Answer 1

It's usually the best approach to define your complete aggregation pipeline separate of the invoking method and following the same rules as structure and indentation as are present in the JSON samples you will find and have used here.

In that way, it becomes a lot easier to see where you deviate from the structure:

List<DBObject> pipeline = Arrays.<DBObject>asList(
    new BasicDBObject("$match",new BasicDBObject("categoryID", 4)),
    new BasicDBObject("$group",
        new BasicDBObject("_id",
            new BasicDBObject("productID","$productID")
                .append("articleID", "$articleID")
                .append("colour", "$colour")
                .append("size",
                    new BasicDBObject("sku","$skuID")
                        .append("size","$size")
                )
        )
    ),
    new BasicDBObject("$group",
        new BasicDBObject("_id",
            new BasicDBObject("productID","$_id.productID")
                .append("articleID", "$_id.articleID")
                .append("colour", "$_id.colour")
        )
        .append("size",new BasicDBObject("$push","$_id.size")
    ),
    new BasicDBObject("$project",
        new BasicDBObject("_id",0)
        .append("productID","$_id.productID")
        .append("colour","$_id.colour")
        .append("size",1)
    )
);

Also note some of the simplified naming here and using $push rather than $addToSet. That last is generally because you already determined unique values by including it in the first $group stage, so an $addToSet would do nothing of value here and in fact would remove any inherent order from the results that would have come from an earlier stage, or if you deliberately ordered.

Much by that token, you can of course just shorten to a single $group as $addToSet does perform it's own "distinct" operation:

List<DBObject> pipeline = Arrays.<DBObject>asList(
    new BasicDBObject("$match",new BasicDBObject("categoryID", 4)),
    new BasicDBObject("$group",
        new BasicDBObject("_id",
            new BasicDBObject("productID","$productID")
                .append("articleID", "$articleID")
                .append("colour", "$colour")
        )
        .append("size",new BasicDBObject("$addToSet",
            new BasicDBObject("sku","$skuID")
                .append("size","$size")
        )
    )
);

As I would also recommend removing that last $project as it essentially needs to pass through all results and alter all documents present. This is just adding to processing that would generally be better handled on the client.

Generally speaking, the less aggregation pipeline stages the better, and unless there is something significant happening, then another software layer is probably better of handling it rather than the database server.