How to include existing fields in mongodb aggregation $project operation

Question

I have a sample collection articles which contains the following data:

/* 0 */
{
    "_id" : "post 1",
    "author" : "Bob",
    "content" : "...",
    "page_views" : 5
}

/* 1 */
{
    "_id" : "post 2",
    "author" : "Bob",
    "content" : "...",
    "page_views" : 9
}

/* 2 */
{
    "_id" : "post 3",
    "author" : "Bob",
    "content" : "...",
    "page_views" : 8
}

I would like to use the aggregation framework to find the min and max value for page views for a given author, and in the process display the _id of the article with the min/max value. This is my expected output:

{ _id    : "Bob", 
   value : { min : { page_views : 5 , _id : "post 1" } , 
             max : { page_views , 9 , _id : "post 3" } } }

I've tried implementing this aggregation pipeline:

db.articles.aggregate([
    {
        "$group":  {
            "_id": "$author",
            "min_page_views": {
                "$min": "$page_views"
            },
            "max_page_views": {
                "$max": "$page_views"
            }
        }            
    },
    {
        "$project": {
            "_id": 1,
            "min": {
                "page_views": "$min_page_views",
                "_id": "$_id"                
            },
            "max": {
                "page_views": "$max_page_views",
                "_id": "$_id"                
            }
        }
    }
])

Output:

/* 0 */
{
    "result" : [ 
        {
            "_id" : "Bob",
            "min" : {
                "page_views" : 5,
                "_id" : "Bob"
            },
            "max" : {
                "page_views" : 9,
                "_id" : "Bob"
            }
        }
    ],
    "ok" : 1
}

I cannot seem to get the other field max._id or min._id which gives the original document id before projection. How do I change my aggregation pipeline so that I can include this field as well?

BatScream · Accepted Answer · 2015-04-02 03:48:13Z

You could modify your aggregate pipeline as below:

Include a $sort stage before your $group stage operator to get the records in ascending order.
Then get the $first and $last records per group which would hold the lowest and highest viewed pages respectively, for each author.

$$ROOT is a system variable, which is used to refer to the top level element, of the currently being processed document.

Modified code:

db.articles.aggregate([
{$sort:{"page_views":1}},
{$group:{"_id":"$author",
         "max":{$last:"$$ROOT"},
         "min":{$first:"$$ROOT"}}},
{$project:{"max.page_views":1,
           "max._id":1,
           "min.page_views":1,
           "min._id":1}}
])

sample o/p:

{
        "_id" : "Bob",
        "max" : {
                "_id" : "post 2",
                "page_views" : 9
        },
        "min" : {
                "_id" : "post 1",
                "page_views" : 5
        }
}

Collectives™ on Stack Overflow

How to include existing fields in mongodb aggregation $project operation

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