I have data and need to group by two fields and in response display unique filed along with how many have on this field. sample data:

[
   {
      "categoryCode":"categoryCode1",
      "categoryName":"categoryName1",
      "cat_id": "A"
   },
   {
      "categoryCode":"categoryCode1",
      "categoryName":"categoryName1",
      "cat_id": "B"
   },
   {
      "categoryCode":"categoryCode2",
      "categoryName":"categoryName2",
      "cat_id": "C"
   }
]

Expected Output:

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

and how to implement in java with Aggregation Object. GroupOperation groupOperation = Aggregation.group("categoryCode", "categoryName").sum(1).as("cat_count"); but getting compile error 1 is not taken inside sum method

CodePudding user response：

1. $group - Group by categoryCode and categoryName. And add aggregate field cat_count for $sum when match with group.
2. $project - Display document for desired output field.

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

Sample Mongo Playground