How to find count based on condition in MOngoDB Aggregation?

Question

I need to get the distinct categoryCode and categoryName and print the count with this combination of how many documents are repeated. here is one condition with match operation gets all the documents based on cat_type , if cat_type does not exist then in category response count show as 0 sample Data:

[
   {
      "cat_id":1,
      "categoryCode":"categoryCode1",
      "categoryName":"categoryName1",
      "cat":[
         {
            "type":"A"
         },
         {
            "type":"B"
         },
         {
            "type":"C"
         }
      ]
   },
   {
      "cat_id":2,
      "categoryCode":"categoryCode1",
      "categoryName":"categoryName1",
      "cat":[
         {
            "type":"A"
         }
      ]
   },
   {
      "cat_id":3,
      "categoryCode":"categoryCode2",
      "categoryName":"categoryName2",
      "cat":[
         {
            "type":"C"
         }
      ]
   },
   {
      "cat_id":4,
      "categoryCode":"categoryCode3",
      "categoryName":"categoryName3",
      "cat":[
         {
            "type":"A"
         }
      ]
   }
]

Expected Output: here match with type='A', category1 and category3 have type='A' then count is print as normal but category2 does not have this cat_type then category2 also show in response with cat_count=0

[
   {
      "categoryCode":"categoryCode1",
      "categoryName":"categoryName1",
      "cat_count": 2
   },
   {
      "categoryCode":"categoryCode2",
      "categoryName":"categoryName2",
      "cat_count": 0
   },
   {
      "categoryCode":"categoryCode3",
      "categoryName":"categoryName3",
      "cat_count": 1
   }
]

I am using query- but query work with the outside field but not work inside array based condition

db.collection.aggregate([
   {
      "$group":{
         "_id":{
            "categoryCode":"$categoryCode",
            "categoryName":"$categoryName"
         },
         "catCount":{
            "$sum":{
               "$cond":{
                  "if":{
                     "$eq":[
                        "$cat.type",
                        "A"
                     ]
                  },
                  "then":1,
                  "else":0
               }
            }
         }
      }
   },
   {
      "$project":{
         "categoryCode":"$_id.categoryCode",
         "categoryName":"$_id.categoryName",
         "catCount":1,
         "_id":0
      }
   }
])

if you unwind then count value repeat by cat_type. but I need count value based on document repeated because cat_type is array and it increase the count — prabhakar srivastava
– prabhakar srivastava, Commented Oct 21, 2021 at 13:06

turivishal · Accepted Answer · 2021-10-21 16:08:49Z

2

Just need to use $in operator condition instead of $eq operator condition,

  {
    $group: {
      _id: {
        "categoryCode": "$categoryCode",
        "categoryName": "$categoryName"
      },
      "cat_count": {
        $sum: {
          $cond: [{ $in: ["A", "$cat.type"] }, 1, 0]
        }
      }
    }
  }

Playground

answered Oct 21, 2021 at 16:08

turivishal

36.4k7 gold badges47 silver badges69 bronze badges

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

3 Comments

prabhakar srivastava Over a year ago

Thanks, it is working now. I need to implement a search with sorting on best match, so is it possible with mongo. i create a question here- stackoverflow.com/questions/69686310/…

prabhakar srivastava Over a year ago

Hi @turivishal, above your solution is correct and you are using 'in' operator and passed only single input "A", if in input sample data one more field parallel to cat_type is description. and need to condition with cat type "A" and description X then how can I write the query.

turivishal Over a year ago

You can try $and operator condition mongoplayground.net/p/G4yxEenwFKO

ray · Accepted Answer · 2021-10-21 13:46:00Z

You can try to evaluate / count the number of type: A element in the cat array by $reduce first. Then $sum the result in the $group to get your answer.

{
    "$group": {
      "_id": {
        "categoryCode": "$categoryCode",
        "categoryName": "$categoryName"
      },
      "catCount": {
        "$sum": {
          "$reduce": {
            "input": "$cat",
            "initialValue": 0,
            "in": {
              "$cond": {
                "if": {
                  $eq: [
                    "$$this.type",
                    "A"
                  ]
                },
                "then": {
                  // add 1 to accumulator if type is A
                  $add: [
                    "$$value",
                    1
                  ]
                },
                // otherwise, simply keep the accumulator as-is
                "else": "$$value"
              }
            }
          }
        }
      }
    }
}

Here is the Mongo playground for your reference.

Hi @ray, above your solution is correct and you are using 'in' operator and passed only single input "A", if in input sample data one more field parallel to cat_type is description. and need to condition with cat type "A" and description X then how can I write the query.
yes, It is expected output, but I was thinking there is a lot of aggregation here on how to do make above aggregation in the Java language.
@prabhakarsrivastava sorry that I am not an expert on java and my help on the adaption would be limited. My suggestion would be asking a new question about the adaption and keep this question focused as it is now.

Collectives™ on Stack Overflow

How to find count based on condition in MOngoDB Aggregation?

2 Answers 2

3 Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Linked

Related