CODEWITHSUNDEEP
mongodb
frostmatthew
frostmatthew

Reputation: 3298

MongoDB find by max value in array of documents

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?

Upvotes: 7

Views: 7610

Answers (2)

innoSPG
innoSPG

Reputation: 4656

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"} } }
])

Upvotes: 16

Saravana
Saravana

Reputation: 12817

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) } }

Upvotes: 1

Related Questions