Spring MongoDB Aggregation Group - Can't get the group query right

Question

I have a document in a MongoDB, which looks like follows.

{
    "_id" : ObjectId("5ceb812b3ec6d22cb94c82ca"),
    "key" : "KEYCODE001",
    "values" : [ 
        {
            "classId" : "CLASS_01",
            "objects" : [ 
                {
                    "code" : "DD0001"
                }, 
                {
                    "code" : "DD0010"
                }
            ]
        }, 
        {
            "classId" : "CLASS_02",
            "objects" : [ 
                {
                    "code" : "AD0001"
                }
            ]
        }
    ]
}

I am interested in getting a result like follows.

{
    "classId" : "CLASS_01",
    "objects" : [ 
        {
            "code" : "DD0001"
        }, 
        {
            "code" : "DD0010"
        }
    ]
}

To get this, I came up with an aggregation pipeline in Robo 3T, which looks like follows. And it's working as expected.

[
    {
        $match:{
            'key':'KEYCODE001'
        }
    },
    {
        "$unwind":{
            "path": "$values",
            "preserveNullAndEmptyArrays": true
        }
    },
    {
        "$unwind":{
            "path": "$values.objects",
            "preserveNullAndEmptyArrays": true
        }
    },
    {
        $match:{
            'values.classId':'CLASS_01'
        }
    },
    {
        $project:{
            'object':'$values.objects',
            'classId':'$values.classId'
        }
    },
    {
        $group:{
            '_id':'$classId',
            'objects':{
                $push:'$object'
            }
        }
    },
    {
        $project:{
            '_id':0,
            'classId':'$_id',
            'objects':'$$objects'
        }
    }
]

Now, when I try to do the same in a SpringBoot application, I can't get it running. I ended up having the error java.lang.IllegalArgumentException: Invalid reference '$complication'!. Following is what I have done in Java so far.

final Aggregation aggregation = newAggregation(
        match(Criteria.where("key").is("KEYCODE001")),
        unwind("$values", true),
        unwind("$values.objects", true),
        match(Criteria.where("classId").is("CLASS_01")),
        project().and("$values.classId").as("classId").and("$values.objects").as("object"),
        group("classId", "objects").push("$object").as("objects").first("$classId").as("_id"),
        project().and("$_id").as("classId").and("$objects").as("objects")
);

What am I doing wrong? Upon research, I found that multiple fields in group does not work or something like that (please refer to this question). So, is what I am currently doing even possible in Spring Boot?

score 2 · Accepted Answer · answered May 28 '19 at 09:05

After hours of debugging + trial and error, found the following solution to be working.

final Aggregation aggregation = newAggregation(
        match(Criteria.where("key").is("KEYCODE001")),
        unwind("values", true),
        unwind("values.objects", true),
        match(Criteria.where("values.classId").is("CLASS_01")),
        project().and("values.classId").as("classId").and("values.objects").as("object"),
        group(Fields.from(Fields.field("_id", "classId"))).push("object").as("objects"),
        project().and("_id").as("classId").and("objects").as("objects")
);

It all boils down to group(Fields.from(Fields.field("_id", "classId"))).push("object").as("objects") that which introduces a org.springframework.data.mongodb.core.aggregation.Fields object that wraps a list of org.springframework.data.mongodb.core.aggregation.Field objects. Within Field, the name of the field and the target could be encapsulated. This resulted in the following pipeline which is a match for the expected.

[
    {
        "$match" :{
            "key" : "KEYCODE001"
        }
    }, 
    {
        "$unwind" :{
            "path" : "$values", "preserveNullAndEmptyArrays" : true
        }
    }, 
    {
        "$unwind" :{
            "path" : "$values.objects", "preserveNullAndEmptyArrays" : true
        }
    }, 
    {
        "$match" :{
            "values.classId" : "CLASS_01"
        }
    }, 
    {
        "$project" :{
            "classId" : "$values.classId", "object" : "$values.objects"
        }
    }, 
    {
        "$group" :{
            "_id" : "$classId",
            "objects" :{
                "$push" : "$object"
            }
        }
    }, 
    {
        "$project" :{
            "classId" : "$_id", "objects" : 1
        }
    }
]

Additionally, figured that there is no need to using $ sign anywhere and everywhere.

Thanks to your post i found a solution to a very similar problem! — EyedPeas, Jun 07 '20 at 12:20
For anyone having a similar problem, look at [this post](https://stackoverflow.com/questions/62225054/mongodb-java-driver-group-with-multiple-fields). — EyedPeas, Jun 07 '20 at 12:26
Mongo can be cool and model-less but its query language is all over the place. Damn I miss SQL. — maaw, Oct 21 '20 at 21:30

Spring MongoDB Aggregation Group - Can't get the group query right

1 Answers1