I have category code and category name with group and other field group with array. sample data-

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

and expected output:

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

how can acheive this with distinct value.

CodePudding user response：

Use $group aggregation stage with $addToSet operator for the respective field.

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

Mongo Playground Sample Execution