CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
user1665355
user1665355

Reputation: 3393

Return specific field in aggregate

i am trying to aggregate the following data:

{
    "_id" : ObjectId("527a6b7c24a8874c078b9d10"),
    "Name" : "FirstName",
    "Link" : "www.mylink.com/123",
    "year" : 2013
}
{
    "_id" : ObjectId("527a6b7c24a8874c078b9d11"),
    "Name" : "FirstName",
    "Link" : "www.mylink.com/124",
    "year" : 2013
}
{
    "_id" : ObjectId("527a6b7c24a8874c078b9d12"),
    "Name" : "SecondName",
    "Link" : "www.mylink.com/125",
    "year" : 2013
}

I want to aggregate number of occurencies of Name field, but also want to return the corresponding Link field in the output of aggregate query. Now I am doing it like this (which does not return the Link field in the output):

db.coll.aggregate([

    { "$match": { "Year": 2013 } },

    { "$group": {
        "_id": {
            "Name": "$Name"
        },
        "count": { "$sum": 1 }
    }},
    { "$project": {
        "_id": "$_id",
        "count": 1
    }},

    { $sort: {
        count: 1
    } }
])

The above returns only Name field and count. But how can I also return the corresponding Link field (could be several) in the output of aggregate query?

Best Regards

Upvotes: 15

Views: 27931

Answers (1)

dyouberg
dyouberg

Reputation: 2332

db.coll.aggregate([
    { "$match": { "year": 2013 } },
    { "$group": {"_id": "$Name", "Link": {$push: "$Link"}, "count": { "$sum": 1 }}},
    { "$project": {"Name": "$_id", _id: 0, "Link": 1, "count": 1}},
    { $sort: {count: 1} }
])

Results in:

{ "Link" : [ "www.mylink.com/125" ], "count" : 1, "Name" : "SecondName" }

{ "Link" : [ "www.mylink.com/123", "www.mylink.com/124" ], "count" : 2, "Name" : "FirstName" }

Ok so the $match was correct except for a typo with 'Year' --> 'year'

The $group could be simplified a little bit. I removed an extra set of brackets so that you get id: 'FirstName' instead of id: { 'name': 'FirstName' } since we can reshape the _id to 'name' in the $project stage anyways.

You needed to add $push or $addToSet to maintain the $Link value in your grouping. $addToSet will allow for unique values in the array only, while $push will add all values, so use whichever at your discretion.

$project and $sort are straightforward, rename and include/exclude whichever fields you would like.

Upvotes: 23

Related Questions