Find the duplicate field name records present inside the nested array object in mongodb

user_3859964 0
Viewed 170

My collection :

[
  {
    "Empname": "Doug",
    "Group": [
      {
        "Category": [
          {
            "Categoryid": 123,
            "Categoryname": "science"
          },
          {
            "Categoryid": 233,
            "Categoryname": "Maths"
          }
        ]
      }
    ]
  },
  {
    "Empname": "stark",
    "Group": [
      {
        "Category": [
          {
            "Categoryid": 123,
            "Categoryname": "science"
          },
          {
            "Categoryid": 144,
            "Categoryname": "language "
          }
        ]
      }
    ]
  }
]

I want to display the following output Here categoryid 123 and categoryname science is present twice.I want to display this duplicate like

{"categoryname":"science","count":2}
mongodb duplicates aggregate count
1 Answers

You have to perform $unwind twice to unwind the nested arrays and perform $group on the Category fields.

Below query is what you are looking for

db.collection.aggregate([
  {
    "$unwind": "$Group"
  },
  {
    "$unwind": "$Group.Category"
  },
  {
    "$group": {
      "_id": {
        "Categoryid": "$Group.Category.Categoryid",
        "Categoryname": "$Group.Category.Categoryname",
        
      },
      "count": {
        "$sum": 1
      }
    }
  },
  
])

Mongo Playground Sample Execution

Edit: Improving with @Takis_ suggestion

db.collection.aggregate([
  {
    "$unwind": "$Group"
  },
  {
    "$unwind": "$Group.Category"
  },
  {
    "$group": {
      "_id": {
        "Categoryid": "$Group.Category.Categoryid",
        "Categoryname": "$Group.Category.Categoryname",
        
      },
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$match": {
      "count": {
        "$gt": 1
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "Categoryname": "$_id.Categoryname",
      "count": 1
    }
  }
])

Mongo Playground Sample Execution

user_82132770
Related