Technologies: Mongoose, NodeJS, MongoDB
Question
The following document will save on the MongoDB cluster every 10 seconds.
{
"_id": "6003fafc04cb3727e40812b2",
"currentRound": 39300,
"current": 4.131929,
"voltage": 245.855,
"power": 956.5797,
"frequency": 50,
"totalPower": 1167.862,
"importPower": 1167.862,
"exportPower": 0,
"powerFactor": 0.998356,
"rssi": -59,
"deviceId": "EC:FA:BC:63:02:C1",
"slaveId": 201,
"timestamp": 1610873596543,
"__v": 0
}
There is two types of slave ids (101, 201) documents saving on the same collection and each slave id is having a specific device id. I want to retrieve the most latest and oldest 101 and 201 containing document by using yesterday's timestamps, starting 0:00 AM to 11:59 PM.
Attempt
I have tried the following solution. but distinct('slaveId') returns only the distinct specific field attribute only.
const latestPGStats = await PGStat
.find({
deviceId: { $in: deviceIds },
timestamp: { $lte: endTimestamp, $gte: startTimestamp }
})
.sort({ timestamp: -1 })
.distinct('slaveId')
.limit(2);
I have seen some peoples suggest using mongo aggregation. but I don't have knowledge about that domain.
You can try aggregate(),
$match your conditions$facet to separate results, first is latest and second is oldest$sort by timestamp in descending order$group by slaveId and get $first document in latest and $last document in oldestconst latestPGStats = await PGStat.aggregate([
{
$match: {
deviceId: { $in: deviceIds },
timestamp: { $lte: endTimestamp, $gte: startTimestamp }
}
},
{ $sort: { timestamp: -1 } },
{
$facet: {
latest: [
{
$group: {
_id: "$slaveId",
root: { $first: "$$ROOT" }
}
},
{ $limit: 1 }
],
oldest: [
{
$group: {
_id: "$slaveId",
root: { $last: "$$ROOT" }
}
},
{ $limit: 1 }
]
}
}
])