Mongo return two lines instead one

Question

I'm having some problem with $max param in mongodb.

I would like to return just a max uptime value.

Query isolating a cenario:

db.monitoraservicos.aggregate([
{"$match" : {"instancia" : "bat1"}

},
{"$group": {"_id": {
                    "instancia": "$instancia",
                    "uptime" :  {"$max" :"$uptime"},
                    "time": "$time"
                    }
           }
},
{"$project" : {
                "instancia" : "$_id.instancia",
                "uptime" : "$_id.uptime",
                "time" : "$_id.time",
                "_id" : 0
            }
},
{"$sort" : {"instancia" : 1}}
        ])

Return :

/* 1 */
{
    "instancia" : "bat1",
    "uptime" : 86,
    "time" : ISODate("2019-09-26T13:37:37.000Z")
}

/* 2 */
{
    "instancia" : "bat1",
    "uptime" : 221,
    "time" : ISODate("2019-09-26T13:37:37.000Z")
}

What I expect :

   {
        "instancia" : "bat1",
        "uptime" : 221,
        "time" : ISODate("2019-09-26T13:37:37.000Z")
    }

• I tried to put $max in $project too, and didn't work

Thank you

Answer 1

Please try this, also check my comment to know on what has gone wrong with your query :

db.monitoraservicos.aggregate([
    {
        "$match": { "instancia": "bat1" }
    },
    {
        $group:
        {
            _id: '$instancia',
            uptime: { $max: "$uptime" },
            time: { $first: '$time' }
        }
    }, { $project: { uptime: 1, time: 1, instancia: '$_id', _id: 0 } }
])

or this is much better which might not need $project stage, it leaves an additional field _id:'' in output, which can optionally be excluded in $project :

db.monitoraservicos.aggregate([
    {
        "$match": { "instancia": "bat1" }
    },
    {
        $group:
        {
            _id: '',
            uptime: { $max: "$uptime" },
            time: { $first: '$time' },
            instancia: { $first: '$instancia' }
        }
    }
])

If you've different timestamps then you need to do this :

db.monitoraservicos.aggregate([
    {
        "$match": { "instancia": "bat1" }

    },
    {
        "$group": {
            "_id": {
                "instancia": "$instancia",
                "uptime": { "$max": "$uptime" },
                "time": "$time"
            }
        }
    },
    {
        "$project": {
            "instancia": "$_id.instancia",
            "uptime": "$_id.uptime",
            "time": "$_id.time",
            "_id": 0
        }
    },
    { "$sort": { "uptime": -1 } }, { $limit: 1 }
])

Actually your dataset seems to have same time for all records matching to { "instancia": "bat1" }, but in case if you've different date in time field for documents then first query will get you the value from first document it found in $group stage irrespective of whether that document's uptime is max value or not (time value can be from 86 as well), but second query should work fine, unless you've 221 in couple of documents, again where time is different for those - that case it would get time from first matching 221 document(altogether just to say, first query is best suitable in most cases).

Try & test on this dataset :

/* 1 */
{
    "_id" : ObjectId("5d8ce9cf8efa15b6d2fc0179"),
    "instancia" : "bat1",
    "servico" : "eal_server",
    "status" : "UP",
    "diahora" : "221d 12h",
    "time" : ISODate("2019-09-26T19:36:37.000Z"),
    "uptime" : 21.0
}

/* 2 */
{
    "_id" : ObjectId("5d8ce9d08efa15b6d2fc017d"),
    "instancia" : "bat1",
    "servico" : "eps_server",
    "diahora" : "221d 12h",
    "status" : "UP",
    "time" : ISODate("2019-09-26T13:36:37.000Z"),
    "uptime" : 221.0
}

/* 3 */
{
    "_id" : ObjectId("5d8ce9d18efa15b6d2fc0181"),
    "instancia" : "bat1",
    "servico" : "fws_server.py",
    "diahora" : "86d 4h",
    "uptime" : 86.0,
    "status" : "UP",
    "time" : ISODate("2019-09-26T13:37:37.000Z")
}

/* 4 */
{
    "_id" : ObjectId("5d8ce9d18efa15b6d2fc0186"),
    "instancia" : "bat1",
    "servico" : "pra_assy_server",
    "time" : ISODate("2019-09-26T13:36:36.000Z"),
    "uptime" : 221.0,
    "diahora" : "221d 12h",
    "status" : "UP"
}

/* 5 */
{
    "_id" : ObjectId("5d8ce9d28efa15b6d2fc018c"),
    "instancia" : "bat1",
    "servico" : "pra_record_server",
    "status" : "UP",
    "time" : ISODate("2019-09-26T13:37:37.000Z"),
    "diahora" : "221d 12h",
    "uptime" : 221.0
}

/* 6 */
{
    "_id" : ObjectId("5d8ce9d38efa15b6d2fc0190"),
    "instancia" : "bat1",
    "servico" : "res_server",
    "status" : "UP",
    "diahora" : "221d 12h",
    "uptime" : 221.0,
    "time" : ISODate("2019-09-26T13:37:37.000Z")
}

/* 7 */
{
    "_id" : ObjectId("5d8ce9d38efa15b6d2fc0194"),
    "instancia" : "bat1",
    "servico" : "seq_file_server",
    "status" : "UP",
    "time" : ISODate("2019-09-26T13:37:37.000Z"),
    "diahora" : "221d 12h",
    "uptime" : 221.0
}

Answer 2

$max accumulator not working inside _id, you need to write $max immediately under the $group

aggregate([{
    "$match": {
        "instancia": "bat1"
    }
},
{
    "$group": {
        "_id": {
            "instancia": "$instancia",
            "time": "$time"
        },
        "uptime": {
            "$max": "$uptime"
        }
    }
},
{
    "$project": {
        "instancia": "$_id.instancia",
        "uptime": "$uptime",
        "time": "$_id.time",
        "_id": 0
    }
},
{
    "$sort": {
        "instancia": 1
    }
}])