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
      }
   }
])

CodePudding user response：

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.

CodePudding user response：

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