Finding duplicate values in a MongoDB array

Question

I have a collection containing entries in following format:

{ 
    "_id" : ObjectId("5538e75c3cea103b25ff94a3"), 
    "userID" : "USER001", 
    "userName" : "manish", 
    "collegeIDs" : [
        "COL_HARY",
        "COL_MARY",
        "COL_JOHNS",
        "COL_CAS",
        "COL_JAMES",
        "COL_MARY",
        "COL_MARY",
        "COL_JOHNS"
    ]
}

I need to find out the collegeIDs those are repeating. So the result should give "COL_MARY","COL_JOHNS" and if possible the repeating count. Please do give a mongo query to find it.

possible duplicate of How to remove duplicate entries from an array? — Thomas
– Thomas, Commented Sep 10, 2015 at 13:09
Please, search for other similar questions before posting your own. I found this through Googling "mongodb find duplicate values array" in under a minute. There are plenty of resources out there to help you with this. Attempt also to show us what you have done. That way we can better guide you. — Thomas
– Thomas, Commented Sep 10, 2015 at 13:11

Cetin Basoz · Accepted Answer · 2015-09-10 13:32:55Z

25

Probably there would be many of these documents and thus you want it per ObjectId.

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

This might be what you want to, not clear from your question:

db.myCollection.aggregate([
  {"$match": {"userID":"USER001"}},
  {"$project": {"collegeIDs":1, "_id":0}},
  {"$unwind":"$collegeIDs"},
  {"$group": {"_id":"$collegeIDs", "count":{"$sum":1}}},
  {"$match": {"count":{"$gt":1}}},
])

edited Sep 10, 2015 at 13:32

answered Sep 10, 2015 at 13:15

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

lime_pal Over a year ago

yes.., i want to find it for a particular user in that collection. i mean filter by giving userid in the query.

Cetin Basoz Over a year ago

Then there is your solution, just ignore the down voter whoever he is he did without thinking. With your data result is: { "_id" : ObjectId("5538e75c3cea103b25ff94a3"), "collegeIDs" : [ "COL_MARY", "COL_JOHNS" ] }

lime_pal Over a year ago

where should i give the user id in that query ?

Cetin Basoz Over a year ago

Ahh you want it for a particular UserID? Then add a $match at top.

red Over a year ago

this perfectly works.

Collectives™ on Stack Overflow

Finding duplicate values in a MongoDB array

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related