Java + MongoDB : Group by name and count values of two different columns based on certain criteria

Question

I am trying to get the desired results on Java platform. To start with, MongoDB's aggregation framework is pretty new to me. So if the question seems trivial, kindly pardon. But I have tried to search for something similar but to no avail.

The original document structure is like this :

{
  "name" : "WS1",
  "previousCount" : 80,
  "currentCount" : 70
},
    {
  "name" : "WS2",
  "previousCount" : 42,
  "currentCount" : 10
},
    {
  "name" : "WS2",
  "previousCount" : 60,
  "currentCount" : 40
},
    {
  "name" : "WS1",
  "previousCount" : 60,
  "currentCount" : 20
},
    {
  "name" : "WS3",
  "previousCount" : 50,
  "currentCount" : 10
},
    {
  "name" : "WS3",
  "previousCount" : 30,
  "currentCount" : 70
},
    {
  "name" : "WS1",
  "previousCount" : 30,
  "currentCount" : 30
},
    {
  "name" : "WS1",
  "previousCount" : 80,
  "currentCount" : 50
},

I have to first group by the documents based on the name, and then apply a filter on the previousCount and currentCount fields and count the number of occurences for which the conditions satisfy.

So if I want to have distinct records which signify the number of times previousCount was >=40 and <=70 and currentCount was >=10 and <=50, I should get something like this:

Thus I would finally want results like :

{
  "name" : "WS1",
  "qualifiedPreviousCount" : 2,
  "qualifiedCurrentCount" : 3
},
    {
  "name" : "WS2",
  "qualifiedPreviousCount" : 2,
  "qualifiedCurrentCount" : 2
},
    {
  "name" : "WS3",
  "qualifiedPreviousCount" : 1,
  "qualifiedCurrentCount" : 1
},

How should I proceed? MongoDB's aggregation framework is something I am finding hard to understand and apply. Help will be much appreciated as I am stuck here for long. Thanks a lot in advance.

Answer 1

You can try below aggregation.

$group by name and $sum with $cond with conditions, when match set 1 else set 0.

db.collection_name.aggregate({
  "$group": {
    "_id": "$name",
    "qualifiedPreviousCount": {
      "$sum": {
        "$cond": [
          {
            "$and": [
              {
                "$gte": [
                  "$previousCount",
                  40
                ]
              },
              {
                "$lte": [
                  "$previousCount",
                  70
                ]
              }
            ]
          },
          1,
          0
        ]
      }
    },
    "qualifiedCurrentCount": {
      "$sum": {
        "$cond": [
          {
            "$and": [
              {
                "$gte": [
                  "$currentCount",
                  10
                ]
              },
              {
                "$lte": [
                  "$currentCount",
                  50
                ]
              }
            ]
          },
          1,
          0
        ]
      }
    }
  }
})

Java code:

MongoClient mongoClient = new MongoClient();
MongoDatabase db = mongoClient.getDatabase("db");
MongoCollection<Document> collection = db.getCollection("collection");
Bson aggregates = Aggregates.group("$name",
                Arrays.asList(Accumulators.sum("qualifiedPreviousCount", new Document("$cond", Arrays.<Object>asList(new Document("$and", Arrays.<Object>asList(
                        new Document("$gte", Arrays.<Object>asList("$previousCount", 40)), new Document("$gte", Arrays.<Object>asList("$previousCount", 70))
                )), 1, 0))), Accumulators.sum("qualifiedCurrentCount", new Document("$cond", Arrays.<Object>asList(new Document("$and", Arrays.<Object>asList(
                        new Document("$gte", Arrays.<Object>asList("$currentCount", 10)), new Document("$gte", Arrays.<Object>asList("$currentCount", 50))
                )), 1, 0)))));

List<Document> results = collection.aggregate(Arrays.asList(aggregates)).into(new ArrayList<>());