I've got the following array of objects (more than 500 objects) obtained from a mongodb collection:
var rentals = [{
deviceId: 1,
start_date: ISODate("2018-05-10T10:11:23.143Z") ,
end_date: ISODate("2018-07-11T12:19:53.143Z")
},{
deviceId: 2,
start_date: ISODate("2018-03-09T10:11:23.143Z") ,
end_date: ISODate("2018-03-10T12:19:53.143Z")
},{
deviceId: 2,
start_date: ISODate("2018-03-11T10:11:23.143Z") ,
end_date: ISODate("2018-05-12T12:19:53.143Z")
},{
deviceId: 3,
start_date: ISODate("2018-01-10T10:11:23.143Z") ,
end_date: ISODate("2018-09-11T12:19:53.143Z")
},{
...
}]
I've got the following readings schema in Mongoose:
var readingSchema = new mongoose.Schema({
deviceId: Number,
timestamp: Date,
data: String
});
Currently 100k readings in the DB.
Readings documents Example:
[{
deviceId: 1,
timestamp: ISODate("2018-05-11T10:11:23.143Z"),
data: 'wathever'
},{
deviceId: 2,
timestamp: ISODate("2018-03-10T00:00:00.000Z"),
data: 'wathever'
},{
deviceId: 2,
timestamp: ISODate("2018-03-09T23:00:00.000Z"),
data: 'wathever'
},{
deviceId: 2,
timestamp: ISODate("2018-05-18T00:00:00.000Z"),
data: 'wathever'
},{
deviceId: 3,
timestamp: ISODate("2018-01-07T00:00:00.000Z"),
data: 'wathever'
},{
...
}]
I need to iterate through the rentals array, and for each rental get the readings of that device that where made between the rental's start_date and the rentals end_date. I need a single readings array as a result.
My solution is iterating through rentals array and doing a query for reach rental.
var allReadings = [];
Async.each(rentals, function(rental, callback) {
Reading.find({
timestamp: {
"$lte": new Date(rental.end_date)
},
timestamp: {
"$gte": new Date(rental.start_date)
},
"deviceId": { rental.deviceId }
},
function(err, readings) {
allReadings.push(readings);
callback();
});
}, function(err){
console.log(allReadings);
});
Is there a way of doing a single Mongoose query and get the same result so that it is performance optimised? I guess I need to use aggregation but can't think of a way of querying this.
So in the example data the result should be:
[{
deviceId: 1,
timestamp: ISODate("2018-05-11T10:11:23.143Z"),
data: 'wathever'
},{
deviceId: 2,
timestamp: ISODate("2018-03-10T00:00:00.000Z"),
data: 'wathever'
},{
deviceId: 2,
timestamp: ISODate("2018-03-09T23:00:00.000Z"),
data: 'wathever'
}]
Edit: the query in sql would be:
SELECT rea.*
FROM ren
INNER JOIN readings rea
ON ren.devideId = rea.deviceId AND ren.start_date <= rea.fecha AND ren.end_date >= rea.timestamp