Home > Net >  Find duplicate name in MongoDB
Find duplicate name in MongoDB

Time:03-08

I'm having a problem in getting the duplicate name in my mongodb to delete duplicates.

{
    "users": [
      {
        "_id": {
          "$oid": "61441890a6566a001623b8ed"
        },
        "name": "Jollibee",
      },
      {
        "_id": {
          "$oid": "61441890a6566a001623b8ed"
        },
        "name": "Jollibee",
      },
      {
        "_id": {
          "$oid": "61441890a6566a001623b8ed"
        },
        "name": "MCDO",
      },
      {
        "_id": {
          "$oid": "61441890a6566a001623b8ed"
        },
        "name": "Burger King",
      },
    ]
  }

I want to show in my output only the duplicate names. which is Jollibee.

tried this approach but it only returns me the count of all the users not the duplicated ones. I want to show 2 Jollibee only.

db.collection.aggregate([
  {
    "$unwind": "$users"
  },
  {
    "$group": {
      "_id": "$_id",
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$match": {
      "_id": {
        "$ne": null
      },
      "count": {
        "$gt": 1
      }
    }
  }
])

CodePudding user response:

Suppose the documents are:

[
    {
        "_id": {
            "$oid": "6226dd742ef592186422ad1d"
        },
        "name": "Stack test"
    },
    {
        "_id": {
            "$oid": "6226dd7d2ef592186422ad1e"
        },
        "name": "Stack test"
    },
    {
        "_id": {
            "$oid": "6226dd912ef592186422ad1f"
        },
        "name": "Stack test 001"
    }
]

Aggreagtion Query:

db.users.aggregate(
    [
        {
            $group: {
                _id: "$name", 
                names: {$push: "$name"}
            }
        }
    ]
)

Result:

{ 
    _id: 'Stack test', 
    names: [ 'Stack test', 'Stack test' ] 
},
{ 
    _id: 'Stack test 001', 
    names: [ 'Stack test 001' ] 
}

But a better way to do it will be

Aggregation Query:

db.users.aggregate(
    [
        {
            $group: {
                _id: "$name", 
                count: {$sum: 1}
            }
        }
    ]
)

Result:

{ 
    _id: 'Stack test',
    count: 2 
},
{ 
    _id: 'Stack test 001', 
    count: 1 
}

Now, you can iterate through the count and use the name value in _id

CodePudding user response:

since the $unwind step gives you same _id for all documents grouping by _id is not correct. Instead try grouping by users.name

db.collection.aggregate([
  {
    "$unwind": "$users"
  },
  {
    "$group": {
      "_id": "$users.name",
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$match": {
      "_id": {
        "$ne": null
      },
      "count": {
        "$gt": 1
      }
    }
  }
])

demo

  • Related