MongoDB Project and Group operations with Spring Data MongoDB

Question

I'd like to use Spring Data MongoDB to execute the following query but I stuck on the project operation and I need some help with it.
Here's a query to translate:

db.getCollection("employee_salaries").aggregate([
    { $match: {
        salaries: {
            $elemMatch: { 
              "to_date": { $gte: "1985-01-01" } ,  
              "to_date": { $lte: "1986-01-01" }
            } 
        }
    }},
    { $project: {
        salaries: {
            $filter: {
                input: "$salaries",
                as: "salaries",
                cond: {$and: [
                    { $gte: ["$$salaries.to_date", "1985-01-01"]},
                    { $lt: ["$$salaries.to_date", "1986-01-01"]}
                ]}
            }
        }
    }},
    { $unwind: "$salaries"},
    { $group: {
        _id: "$null",
        "total_salary": { $sum: "$salaries.salary"}
    }}
])

And here's my attempt to do so:

@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class SimpleService {

    private final MongoTemplate mongoTemplate;

    public Document aggregation() {

        MatchOperation matchOperation = match(Criteria.where("salaries").elemMatch(Criteria.where("to_date").gte("1985-01-01").lte("1986-01-01")));
        ProjectionOperation projectionOperation = project("salaries");
        // other operations
        UnwindOperation unwindOperation = unwind("salaries");

        Document rawResults = mongoTemplate.aggregate(newAggregation(
                matchOperation,
                // other operations...
        ), Salaries.class, String.class).getRawResults();

        return totalSalaryInOneYear;
    }
}

RLD · Accepted Answer · 2020-09-14 07:37:44Z

3

Code for the ProjectionOperation and GroupOperation with required imports is given below.

import static org.springframework.data.mongodb.core.aggregation.Aggregation.group;
import static org.springframework.data.mongodb.core.aggregation.Aggregation.project;
import static org.springframework.data.mongodb.core.aggregation.ArrayOperators.Filter.filter;
import org.springframework.data.mongodb.core.aggregation.BooleanOperators.And;
import org.springframework.data.mongodb.core.aggregation.ComparisonOperators.Gte;
import org.springframework.data.mongodb.core.aggregation.ComparisonOperators.Lt;

ProjectionOperation projectionOperation =  project().and(filter("salaries")
                 .as("salaries")
                 .by(And.and(Gte.valueOf("salaries.to_date").greaterThanEqualToValue("1985-01-01"),Lt.valueOf("salaries.to_date").lessThanValue("1986-01-01"))))
                 .as("salaries");

As you do group by null, no need to pass any id.

GroupOperation groupOperation = group().sum("salaries.salary").as("tot‌al_salary");

In case above does not work, you need to pass some non-existing field to group() method. Ensure the parameter passed is not part of your collection.

GroupOperation groupOperation = group("NotAField").sum("salaries.salary").as("tot‌al_salary");

Any further issue, let me know.

edited Sep 14, 2020 at 7:37

answered Sep 12, 2020 at 20:44

RLD

2,0273 gold badges18 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

big_OS Over a year ago

Thank you very much! One more thing - could you show me how to write the GroupOperation for the query as well? I started with GroupOperation groupOperation = group().push("_id").as("_id").sum("salaries.salary").as("total_salary"); I mentioned it in the title but forgot to do it later in text. Thank you in advance!

Collectives™ on Stack Overflow

MongoDB Project and Group operations with Spring Data MongoDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related