mongodb aggregation $max and corresponding timestamp

Question

I'm being challenged by the $group $max in an aggregation with MongoDB on Nodes Express app. Here is the a sample of the collection;

{"_id":"5b7e78cf022be03c35776bec",
"humidity":60,
"pressure":1014.18,
"temperature":26.8,
"light":2464,
"timestampiso":"2018-08-23T09:05:19.112Z",
"timestamp":1535015119112
},
{
"_id":"5b7e7892022be03c35776bea",
"humidity":60.4,
"pressure":1014.14,
"temperature":26.7,
"light":2422,
"timestampiso":"2018-08-23T09:04:18.115Z",
"timestamp":1535015058115
},
{
"_id":"5b7e7855022be03c35776be8",
"humidity":60.6,
"pressure":1014.2,
"temperature":26.6,
"light":2409,
"timestampiso":"2018-08-23T09:03:17.113Z",
"timestamp":1535014997113
}]

What I'm trying to do is to query the collection, by first retrieving the entries of the last hour based on the timestamp and then looking for highest pressure of the sample (should be 60 entries as there is one entry per minute)

What I can de is find this value. What I'm stuggling on to have the timestamp related to that max value.

When I run

db.collection("ArduinoSensorMkr1000").
  aggregate([{ "$match" : {"timestamp" : {"$gte" : (Date.now()-60*60*1000)}}},
     { "$group" : {"_id" : null, maxpressure : {"$max" : "$pressure"}
                  }
      },
      {
        "$project" : { "_id" : 0 }
      }
  ])

Fine, the output is correct and I get the maxpressure as so

[{"maxpressure":1014.87}]

but what I'm trying to output is the maxpressure field but with it, its corresponding timestamp. The output should look as so

[{"maxpressure":1014.87,"timestamp":1535015058115}]

Any hints on how I get this timestamp value to show?

Thank you for your support

Senthur Deva · Answer 1 · 2018-08-23T10:21:16.977

You can try this first need to sort your data using $sort and you can pick max value by using $first

QUERY

db.col.aggregate([
    { "$match": { "timestamp": { "$gte": (Date.now() - 60 * 60 * 1000) } } },
    { "$sort": { "pressure": -1 } },
    {
        "$group": {
            "_id": null, "maxpressure": { "$first": "$pressure" },
            "timestamp": { "$first": "$timestamp" }
        }
    },
    {
        "$project": { "_id": 0 }
    }
])

DATA

[{
    "_id" : "5b7e78cf022be03c35776bec",
    "humidity" : 60.0,
    "pressure" : 1014.18,
    "temperature" : 26.8,
    "light" : 2464.0,
    "timestampiso" : "2018-08-23T09:05:19.112Z",
    "timestamp" : 1535015119112.0
},
{
    "_id" : "5b7e7892022be03c35776bea",
    "humidity" : 60.4,
    "pressure" : 1014.14,
    "temperature" : 26.7,
    "light" : 2422.0,
    "timestampiso" : "2018-08-23T09:04:18.115Z",
    "timestamp" : 1535015058115.0
},
{
    "_id" : "5b7e7855022be03c35776be8",
    "humidity" : 60.6,
    "pressure" : 1014.2,
    "temperature" : 26.6,
    "light" : 2409.0,
    "timestampiso" : "2018-08-23T09:03:17.113Z",
    "timestamp" : 1535014997113.0
}]

THE OUTPUT

{
    "maxpressure" : 1014.87,
    "timestamp" : 1535015058115.0
}

Hi, thanks for the support. It works. Should have thought of the $sort. — PappyG, Aug 24 '18 at 08:36

score 0 · Answer 2 · answered Aug 23 '18 at 10:23

My suggestion is to use sort/limit instead of grouping. By this way you can get entire document before project only interesting fields :

db['ArduinoSensorMkr1000'].aggregate(
[{ "$match" : {"timestamp" : {"$gte" : (Date.now()-5*60*60*1000)}}},

  {$sort:{pressure:-1}},
  {$limit:1},{

        "$project" : { "_id" : 0,"timestamp":1,"pressure":1 }}

  ])

mongodb aggregation $max and corresponding timestamp

2 Answers2