MongoDB find by max value in array of documents

Question

Given a collection with documents such as:

{
    "host" : "example.com",
    "ips" : [
        {
            "ip" : NumberLong("1111111111"),
            "timestamp" : NumberLong(1373970044)
        },
        {
            "ip" : NumberLong("2222222222"),
            "timestamp" : NumberLong(1234978746)
        }
    ]
}

I need to return all documents with an ip value of X, but only if the associated timestamp for X is the highest timestamp in the ips array (so the above example document should not match a search for "2222222222" because that is not the IP with the most recent timestamp).

This is my first time doing anything much beyond fairly basic stuff in MongoDB so the closest I've been able to get is:

coll.aggregate({$match:{"ips.ip":X}},{$group:{"_id":"$host", "max":{$max:"$ips.timestamp"}}},{$sort:{"ips.timestamp":-1}}).result

Which obviously doesn't give me what I'm looking for, it returns anything with an ips.ip value of X. How do I return only documents where ip.ip is X only if X's associated timestamp is the highest for that ips array?

Answer 1

If host is unique, the following code should do the job. Otherwise, you can simply replace host by _id in the grouping operation:

coll.aggregate([
  {$unwind: "$ips"},
  {$project:{host:"$host",ip:"$ips.ip", ts:"$ips.timestamp"} },
  {$sort:{ts:1} },
  {$group: {_id: "$host", IPOfMaxTS:{$last: "$ip"}, ts:{$last: "$ts"} } }
])

Answer 2

you can use $reduce in aggregation pipeline starting from mongo 3.4 version

db.t64.aggregate([
    {$addFields : {ips : {$reduce : {
        input : "$ips", 
        initialValue : {timestamp : 0}, 
        in : {$cond: [{$gte : ["$$this.timestamp", "$$value.timestamp"]},"$$this", "$$value"]}}
    }}}
])

sample collection

> db.t64.findOne()
{
        "_id" : ObjectId("5c45e00f328877e101354d97"),
        "host" : "example.com",
        "ips" : [
                {
                        "ip" : NumberLong(1111111111),
                        "timestamp" : NumberLong(1373970044)
                },
                {
                        "ip" : NumberLong("2222222222"),
                        "timestamp" : NumberLong(1234978746)
                }
        ]
}

output

> db.t64.aggregate([ {$addFields : {ips : {$reduce : { input : "$ips",  initialValue : {timestamp : 0},  in : {$cond: [{$gte : ["$$this.timestamp", "$$value.timestamp"]},"$$this", "$$value"]}} }}} ])
{ "_id" : ObjectId("5c45e00f328877e101354d97"), "host" : "example.com", "ips" : { "ip" : NumberLong(1111111111), "timestamp" : NumberLong(1373970044) } }