How to Find Duplicate Elements in a array (One Document)?

Question

How do I return duplicate elements in an array (ignoring case) present in MongoDB?

Input (document in MongoDB)

 {
    "userID" : "USER001",
    "userName" : "manish",
    "collegeIDs" : [ 
        "COL_HARY", 
        "COL_MARY", 
        "COL_JOHNS", 
        "COL_CAS", 
        "COL_JAMES", 
        "col_mary", 
        "COL_JOHNS", 
        "COL_JOHNS"
    ]
}

Expected Output:

{ "collegeIDs" : ["COL_MARY", "col_mary", "COL_JOHNS"] }

Note:

It should only check the content and not the case.
Return all the possibilities in the list

What I have tried

db.myList.aggregate([
  {"$project": {"collegeIDs":1}},
  {"$unwind":"$collegeIDs"},
  {"$project": {"collegeIDs": {"$toLower": "$collegeIDs"}}},
  {"$group": {"_id":{"_id":"$_id", "cid":"$collegeIDs"}, "count":{"$sum":1}}},
  {"$match": {"count":{"$gt":1}}},
  {"$group": {"_id": "$_id._id", "collegeIDs": {"$addToSet":"$_id.cid"}}}
])

Possible duplicate of Finding duplicate values in a MongoDB array — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Aug 6, 2019 at 15:18

Himanshu Sharma · Accepted Answer · 2019-08-06 18:08:42Z

The following query can get you the expected output:

db.check.aggregate([
    {
        $unwind:"$collegeIDs"
    },
    {
        $addFields:{
            "collegeIdToLower":{
                $toLower:"$collegeIDs"
            }
        }
    },
    {
        $group:{
            "_id":"$collegeIdToLower",
            "collegeIDs":{
                $addToSet:"$collegeIDs"
            },
            "count":{
                $sum:1
            }
        }
    },
    {
        $match:{
            "count":{
                $gt:1
            }
        }
    },
    {
        $unwind:"$collegeIDs"
    },  
    {
        $group:{
            "_id":"NO_ID",
            "collegeIDs":{
                $push: "$collegeIDs"
            }
        }
    },
    {
        $project:{
            "_id":0
        }
    }
]).pretty()

Output:

{
    "collegeIDs" : [
        "col_mary",
        "COL_MARY",
        "COL_JOHNS"
    ]
}

Instead of converting the actual college ID to lower string, we would add one more field for that and use for grouping.

Collectives™ on Stack Overflow

How to Find Duplicate Elements in a array (One Document)?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related