MongoDB aggregate query to SpringDataMongoDB

Question

I have below MongoDB aggregate query and would like to have it's equivalent SpringData Mongodb query.

MongoDB Aggregate Query :

db.response.aggregate(
        // Pipeline
        [
            // Stage 1 : Group by Emotion & Month
            {
                $group: {
                    _id: {
                        emotion: "$emotion",
                        category: "$category"
                    },
                    count: {
                        $sum: 1
                    },
                    point: {
                        $first: '$point'
                    }
                }
            },
            // Stage 2 : Total Points
            {
                $addFields: {
                    "totalPoint": {
                        $multiply: ["$point", "$count"]
                    }
                }
            },
                        // Stage3 : Group By Category - Overall Response Total & totalFeedbacks
            {
                $group: {
                    _id: '$_id.category',
                    totalFeedbacks: {
                        $sum: "$count"
                    },
                    overallResponseTotal: {
                        $sum: "$totalPoint"
                    }
                }
            },
                        // Stage4 - Overall Response Total & totalFeedbacks
            {
                $project: {
                    _id: 1,
                    overallResponseTotal: '$overallResponseTotal',
                    maxTotalFrom: {
                        "$multiply": ["$totalFeedbacks", 3.0]
                    },
                    percent: {
                        "$multiply": [{
                            "$divide": ["$overallResponseTotal", "$maxTotalFrom"]
                        }, 100.0]
                    }
                }
            },
                        // Stage4 - Percentage Monthwise
                {
                    $project: {
                        _id: 1,
                        overallResponseTotal: 1,
                        maxTotalFrom: 1,
                        percent: {
                            "$multiply": [{
                                "$divide": ["$overallResponseTotal", "$maxTotalFrom"]
                            }, 100.0]
                        }
                    }

                }
            ]
);

I have tried it's equivalent in Spring Data but got stuck at Stage 2 on how to convert "$addFields" to java code. Though I search about it on multiple sites but couldn't find anything useful. Please see my equivalent java code for Stage 1.

//Stage 1 -Group By Emotion and Category and return it's count

GroupOperation groupEmotionAndCategory = Aggregation.group("emotion","category").count().as("count").first("point")
                        .as("point");

Aggregation aggregation = Aggregation.newAggregation(groupEmotionAndCategory);

AggregationResults<CategoryWiseEmotion> output = mongoTemplate.aggregate(aggregation, Response.class, CategoryWiseEmotion.class);

Any helps will be highly appreciated.

score 2 · Answer 1 · answered Dec 05 '17 at 12:23

2

$addFields is not yet supported by Spring Data Mongodb.

One workaround is to pass the raw aggregation pipeline to Spring.

But since you have a limited number of fields after stage 1, you could also downgrade stage 2 to a projection:

{
    $project: {
        // _id is included by default
        "count" : 1, // include count
        "point" : 1, // include point
        "totalPoint": {
            $multiply: ["$point", "$count"] // compute totalPoint
        }
    }
}

I haven't tested it myself, but this projection should translate to something like:

ProjectionOperation p = project("count", "point").and("point").multiply(Fields.field("count")).as("totalPoint");

Then you can translate stage 3, 4 and 5 similarly and pass the whole pipeline to Aggregation.aggregate().

answered Dec 05 '17 at 12:23

Marc Tarin

3,109
17
49

Thanks @Marc Tarin, it worked ! But now I am stuck at Stage 4 to do multiply and divide. Could you please help me. – Beginner Dec 05 '17 at 12:59
Check this [thread](https://stackoverflow.com/a/42033298/5873923) and try with `project(...).andExpression("overallResponseTotal / maxTotalFrom * 100.0").as("percent")`. – Marc Tarin Dec 05 '17 at 13:20
Marc Tarin , For // Stage3 : Group By Category - Overall Response Total & totalFeedbacks. I have written this in Java `GroupOperation groupCategoryOverAllResponseAndTotalFeedback = Aggregation.group("_id.category").sum("count") .as("totalFeedbacks").sum("totalPoint").as("overallResponseTotal");`. But it is giving me error as Invalid reference _id.category – Beginner Dec 06 '17 at 08:07
I don't know the rationale behind this, but the Spring aggregation framework does not accept "_id" as a valid field name for certain aggregation stages. You have to tinker a bit to find a workaround, maybe project category in stage 2 and refer to it in stage 3. Remember you still have the possibility to pass the raw aggregation to Spring. It helps speed up development, then you can come back later to it when you're more familiar with the Spring aggregation framework dos and donts. – Marc Tarin Dec 06 '17 at 09:27

MongoDB aggregate query to SpringDataMongoDB

1 Answers1