0

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
Egidi
  • 1,736
  • 8
  • 43
  • 69
  • Possible dupe of https://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection. Use `$filter` – s7vr Jul 11 '18 at 13:09
  • @Veeram I am not sure if your link helps..how do you look for all the rentals in the array at once? – Egidi Jul 11 '18 at 15:59
  • You look one array value at a time using $filter and output the array elements which match your query criteria. – s7vr Jul 11 '18 at 16:23
  • So what should be the output from the above document? – Ashh Jul 11 '18 at 16:58
  • @AnthonyWinzlet the output should be array of readings – Egidi Jul 11 '18 at 18:00
  • Can you please give me the criteria and the output from the above array... Criteria in the sense start and end date – Ashh Jul 11 '18 at 18:02
  • @AnthonyWinzlet I edited the OP with more example data. The criteria is: I need to obtain all the readings which timestamp is between start_date-end_date of a rental and deviceId matches with the rental too. Thanks for your help in advance! – Egidi Jul 11 '18 at 18:35
  • @AnthonyWinzlet added sql equivalent – Egidi Jul 12 '18 at 12:31
  • have you tried [`$lookup`](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/) ? Something like `db.rentals.aggregate([ { $lookup: { from: "readings", localField: "deviceId", foreignField: "deviceId", as: "mreadings" } }, {$unwind:"$mreadings"}, {$match:{"start_date":{$lte:"$mreadings.timestamp"},"end_date":{$gte:"$mreadings.timestamp"} } ])` – s7vr Jul 12 '18 at 12:31

0 Answers0