MongoDB - Find first and last in the embedded array for a single matching document

Question

I have a collection of websites, which each contain a list of websites and their keywords that are being tracked. I also have another collection called "rankings" which for each of the keywords in the website contains a ranking. The collection so far looks like this:

{
    "_id" : ObjectId("58503934034b512b419a6eab"),
    "website" : "https://www.google.com",
    "name" : "Google",
    "keywords" : [ 
        "Search", 
        "Websites", 
    ],
    "tracking" : [ 
        {
            "_id" : ObjectId("5874aa1df63258286528598d"),
            "position" : 0,
            "created_at" : ISODate("2017-01-1T09:32:13.831Z"),
            "real_url" : "https://www.google.com",
            "keyword" : "Search"
        }, 
        {
            "_id" : ObjectId("5874aa1ff63258286528598e"),
            "keyword" : "Search",
            "real_url" : "https://www.google.com",
            "created_at" : ISODate("2017-01-2T09:32:15.832Z"),
            "found_url" : "https://google.com/",
            "position" : 3
        }, 
        {
            "_id" : ObjectId("5874aa21f63258286528598f"),
            "keyword" : "Search",
            "real_url" : "https://www.foamymedia.com",
            "created_at" : ISODate("2017-01-3T09:32:17.017Z"),
            "found_url" : "https://google.com/",
            "position" : 2
        }, 

        {
            "_id" : ObjectId("5874aa21f63258286528532f"),
            "keyword" : "Websites",
            "real_url" : "https://www.google.com",
            "created_at" : ISODate("2017-01-1T09:32:17.017Z"),
            "found_url" : "https://google.com/",
            "position" : 1
        }, 

         {
            "_id" : ObjectId("5874aa21f63258286528542f"),
            "keyword" : "Websites",
            "real_url" : "https://www.google.com",
            "created_at" : ISODate("2017-01-1T09:32:17.017Z"),
            "found_url" : "https://google.com/",
            "position" : 2
        }, 

    ]
}

What I want to do is:

1) Group all of the keywords together by their keyword

2) Find the starting position (at the very start of the month)

3) Find the current position (as of today)

So in theory I want to be given an object like:

{
    "_id" : ObjectId("58503934034b512b419a6eab"), 
    "website" : "https://www.google.com",

    "tracking" : [
       {
           "_id" : ObjectId("5874aa1df63258286528598d"), 
           "keyword": "Search",
           "start_position": 0, 
           "todays_position": 3, 

       }, 

        {
           "_id" : ObjectId("5874aa1df63258286528598d"), 
           "keyword": "Website",
           "start_position": 0, 
           "todays_position": 2, 

       }, 


    ]

I am confused about how to do the grouping on another field, though. I have tried the following so far:

db.getCollection('websites').aggregate([

    {
        $lookup: {
            from: "seo_tracking", 
            localField: "website",
            foreignField: "real_url",
            as: "tracking"
        }
    },

    {
        $match: {
            "_id" : ObjectId("58503934034b512b419a6eab")
        }
    },

    {
        $group: {
            "_id" : "$_id", 
            "keyword" : {
                $first: "$tracking.keyword",
            },
        }
    }

]); 

But this is not grouping by the keyword, nor can I figure out how I would get the expected value.

Answer 1

You can try something like this. $unwind the tracking array followed by $sort on tracking.keyword and tracking.created_at. $group by tracking.keyword and $first to get starting position, $avg to get average position and $last to get the today's position. Final $group to roll up everything back to tracking array.

db.website.aggregate([{
    $match: {
        "_id": ObjectId("58503934034b512b419a6eab")
    }
}, {
    $lookup: {
        from: "seo_tracking",
        localField: "website",
        foreignField: "real_url",
        as: "tracking"
    }
}, {
    $unwind: "$tracking"
}, {
    $sort: {
        "tracking.keyword": 1,
        "tracking.created_at": -1
    }
}, {
    $group: {
        "_id": "$tracking.keyword",
        "website": {
            $first: "$website"
        },
        "website_id": {
            $first: "$_id"
        },
        "avg_position": {
            $avg: "$tracking.position"
        },
        "start_position": {
            $first: "$tracking.position"
        },
        "todays_position": {
            $last: "$tracking.position"
        }
    }
}, {
    $group: {
        "_id": "$website_id",
        "website": {
            $first: "$website"
        },
        "tracking": {
            $push: {
                "keyword": "$_id",
                "avg_position":"$avg_position",
                "start_position": "$start_position",
                "todays_position": "$todays_position"
            }
        }
    }
}]);