MongoDb aggregation query using $project and $sort

Question

My MongoDb collection is as follows

{
    "_id" : ObjectId("5a187babdbf0a03cdca0d0bc"),
    "aggregationDate" : "2017-10-31",
    "ipaddress" : "10.65.66.184",
    "first" : {
        "count" : 3
    },
    "second" : {
        "count" : 2
    },
    "third" : {
        "count" : 3
    },
}

{
    "_id" : ObjectId("5a187babdbf0a03cdca0d0bd"),
    "aggregationDate" : "2017-10-31",
    "ipaddress" : "10.65.66.182",
    "first" : {
        "count" : 4
    },
    "second" : {
        "count" : 10
    },
    "third" : {
        "count" : 4
    },
}

{
    "_id" : ObjectId("5a187babdbf0a03cdca0d0be"),
    "aggregationDate" : "2017-10-31",
    "ipaddress" : "10.65.66.189",
    "first" : {
        "count" : 3
    },
    "second" : {
        "count" : 1
    },
    "third" : {
        "count" : 12
    },
}

I want to display the document that has highest sum of count of first, count of second and count of third.

In this case, the output should be -

{
    "_id" : ObjectId("5a187babdbf0a03cdca0d0bd"),
    "aggregationDate" : "2017-10-31",
    "ipaddress" : "10.65.66.182",
    "first" : {
        "count" : 4
    },
    "second" : {
        "count" : 10
    },
    "third" : {
        "count" : 4
    },
}

I only need one document as output.

db.getCollection('foo').aggregate(
{
    $project: {
        _id: "$ipaddress",
        max: { $max: { $add: [ "$first.count", "$second.count", "$third.count"] } }
        }
 },       
 { $sort: { refCount: -1 }}
 )

I get the following exception

"errmsg" : "exception: invalid operator '$max'"

Can someone please help me with this query? Or what i am doing wrong.

chridam · Accepted Answer · 2017-11-26 17:02:10Z

9

You need to create a pipeline that creates the extra refCount field to hold the total count. The first pipeline would be $addField as it allows you to add new fields to the document. The sum is made possible with the $add operator.

The preceding pipeline step would then be the $sort to order the documents by the new field descending.

The final step $limit will return a single document:

db.getCollection('foo').aggregate([
    {
        "$addFields": {
            "refCount": {
                "$add": ["$first.count", "$second.count", "$third.count"]
             }
        }
    },
    { "$sort": { "refCount": -1 } },
    { "$limit": 1 }
])

answered Nov 26, 2017 at 17:02

chridam

104k26 gold badges246 silver badges243 bronze badges

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

4 Comments

Sad Mad Over a year ago

Thanks buddy, you explained the answer wonderfully as well. Also, in this case, I only get output as refCount. Is there any way to display the other fields such as "_id" : ObjectId("5a187babdbf0a03cdca0d0bd"), "aggregationDate" : "2017-10-31", "ipaddress" : "10.65.66.182"?

Sad Mad Over a year ago

Also, more importantly, I have an additional question. I have one document, per ipaddress, per day. How do I find the ipaddress most active over all the days? The above query gives me the highest for one particular day.. But how do i compute a highest based on, value of $add over all the days?

Charly berthet Over a year ago

Any solution ??

chridam Over a year ago

@Charlyberthet Solution to which question?

Collectives™ on Stack Overflow

MongoDb aggregation query using $project and $sort

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related