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"
   },
   {
      "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 cat_type='A', category1 and category3 have cat_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 this query- https://mongoplayground.net/p/EasRK2xyLkG

db.collection.aggregate([
  {
    "$match": {
      "cat_type": "A"
    }
  },
  {
    $group: {
      _id: {
        "categoryCode": "$categoryCode",
        "categoryName": "$categoryName"
      },
      "cat_count": {
        $sum: 1
      }
    }
  },
  {
    $project: {
      "_id": 0,
      "categoryCode": "$_id.categoryCode",
      "categoryName": "$_id.categoryName",
      "cat_count": "$cat_count"
    }
  }
])

with this query, 2nd category is not match so in response it it not showing.

CodePudding user response：

• remove $match stage
• check condition while sum, if cat_type is "A" then return 1 otherwise 0

  {
    $group: {
      _id: {
        "categoryCode": "$categoryCode",
        "categoryName": "$categoryName"
      },
      "cat_count": {
        $sum: {
          $cond: [{ $eq: ["$cat_type", "A"] }, 1, 0]
        }
      }
    }
  }

Playground