Selecting Distinct values from Array in MongoDB

Question

I have a collection name Alpha_Num, It has following structure. I am trying to find out which Alphabet-Numerals pair will appear maximum number of times ?

If we just go with the data below, pair abcd-123 appears twice so as pair efgh-10001, but the second one is not a valid case for me as it appears in same document.

{
    "_id" : 12345,
    "Alphabet" : "abcd",
        "Numerals" : [
            "123",
            "456",
            "2345"
        ]
}
{
    "_id" : 123456,
    "Alphabet" : "efgh",
    "Numerals" : [
            "10001",
            "10001",
            "1002"
        ]
}

{
    "_id" : 123456567,
    "Alphabet" : "abcd",
        "Numerals" : [
            "123"
        ]
}

I tried to use aggregation frame work, something like below

db.Alpha_Num.aggregate([
                     {"$unwind":"$Numerals"},
                     {"$group":
                              {"_id":{"Alpha":"$Alphabet","Num":"$Numerals"},
                               "count":{$sum:1}}
                     },
                     {"$sort":{"count":-1}}
                     ])

Problem in this query is it gives pair efgh-10001 twice. Question : How to select distinct values from array "Numerals" in the above condition ?

Tushar Gupta - curioustushar · Accepted Answer · 2015-07-11 18:41:23Z

23

Problem solved.

db.Alpha_Num.aggregate([{
     "$unwind": "$Numerals"
}, {
     "$group": {
             _id: {
                     "_id": "$_id",
                     "Alpha": "$Alphabet"
             },
             Num: {
                     $addToSet: "$Numerals"
             }
     }
}, {
     "$unwind": "$Num"
}, {
     "$group": {
             _id: {
                     "Alplha": "$_id.Alpha",
                     "Num": "$Num"
             },
             count: {
                     "$sum": 1
             }
     }
}])

Grouping using $addToSet and unwinding again did the trick. Got the answer from one of 10gen online course.

edited Jul 11, 2015 at 18:41

Tushar Gupta - curioustushar

57.2k24 gold badges106 silver badges110 bronze badges

answered Apr 22, 2013 at 3:55

Srivatsa N

2,3214 gold badges24 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Selecting Distinct values from Array in MongoDB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related