[FIXED] How to optimize Multiple countDocuments calls into one aggregated call

Issue

In Java 11, I have a function that makes multiple MongoDb .countDocuments(query) calls on the same collection with 3 different queries. Is there a way to aggregate these three calls into one request so that the MongoDB service isn't called 3 separate times. I'm currently using Bson and the com.mongodb.client.model.Filters.* library to build the queries individually. However I haven't found a good resource on how to do this with BSON and I am open to other methods to accomplish this.

My function includes something like this..

import org.springframework.data.mongodb.core.MongoOperations;
private final MongoOperations mongoOperations;

public RequestCount Foo (){
   Bson query1 = eq("field1", "foo" )
   Bson query2 = eq("field2", "bar" )
   Bson query3 = eq("field3", "raw" )

   count1 = mongoOperations.getCollection("collection").countDocuments(query1);
   count2 = mongoOperations.getCollection("collection").countDocuments(query2);
   count3 = mongoOperations.getCollection("collection").countDocuments(query3);

   return RequestCount(count1, count2, count3);
}

I then store these in an Standard PoJo object with 3 private fields like...

public class RequestCount {

    private Integer count1;
    private Integer count2;
    private Integer count3;
   
}

Solution

You can combine the $facet and $project operations in one aggregation pipeline like this:

db.getCollection("fooBarDocument")
    .aggregate([
            {
                $facet: {
                    "field1Foo": [{$match: {"field1": {$eq: "foo"}}}, {$count: "count"}],
                    "field2Bar": [{$match: {"field2": {$eq: "bar"}}}, {$count: "count"}],
                    "field3Raw": [{$match: {"field3": {$eq: "raw"}}}, {$count: "count"}],
                }
            },
            {
                $project: {
                    "count1": {$arrayElemAt: ["$field1Foo.count", 0]},
                    "count2": {$arrayElemAt: ["$field2Bar.count", 0]},
                    "count3": {$arrayElemAt: ["$field3Raw.count", 0]}
                }
            }
        ]
    )

You could write it in Spring Data like this:

import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.ArrayOperators;
import org.springframework.data.mongodb.core.aggregation.FacetOperation;
import org.springframework.data.mongodb.core.aggregation.ProjectionOperation;
import org.springframework.data.mongodb.core.aggregation.TypedAggregation;
import org.springframework.data.mongodb.core.query.Query;


@Repository
// ...
public class MongoFooBarRepository implements FooBarRepository {
    private static final String FIELD_1 = "field1";
    private static final String FIELD_2 = "field2";
    private static final String FIELD_3 = "field3";

    private static final String FIELD_1_FOO = "field1Foo";
    private static final String FIELD_2_BAR = "field2Bar";
    private static final String FIELD_3_RAW = "field3Raw";
    /// ...

    private FooBarCounts countFooBarRawOccurrences() {
        FacetOperation facet = facet(match(where(FIELD_1).is("foo")), count().as(COUNT)).as(FIELD_1_FOO)
                .and(match(where(FIELD_2).is("bar")), count().as(COUNT)).as(FIELD_2_BAR)
                .and(match(where(FIELD_3).is("raw")), count().as(COUNT)).as(FIELD_3_RAW);

        ProjectionOperation projectionOperation = project()
                .and(ArrayOperators.ArrayElemAt.arrayOf(FIELD_1_FOO + "." + COUNT).elementAt(0)).as(COUNT_1)
                .and(ArrayOperators.ArrayElemAt.arrayOf(FIELD_2_BAR + "." + COUNT).elementAt(0)).as(COUNT_2)
                .and(ArrayOperators.ArrayElemAt.arrayOf(FIELD_3_RAW + "." + COUNT).elementAt(0)).as(COUNT_3);

        TypedAggregation<FooBarDocument> aggregation = Aggregation
                .newAggregation(FooBarDocument.class, facet, projectionOperation);

        return mongoTemplate.aggregate(aggregation, FooBarCounts.class).getMappedResults().get(0);
    }

Additionally, to make the result return 0 instead of null in case there is not even one occurrence of the expected value for a given field, you can define the constructor like this:

    public FooBarCounts(Integer count1, Integer count2, Integer count3) {
        this.count1 = count1 == null ? 0 : count1;
        this.count2 = count2 == null ? 0 : count2;
        this.count3 = count3 == null ? 0 : count3;
    }

Other answers I've found useful to answer this question:

• Multiple Counts with single query in mongodb
• Convert $facet mongo query to Spring Data (has some compilation issues but points out the right direction of using the FacetOperation)

---

Bonus:

working example https://bitbucket.org/kasptom/stackoverflow-73339478-mongo-foobar-count

Answered By - kasptom
Answer Checked By - Mary Flores (JavaFixing Volunteer)