yadbo yadbo - 5 months ago 19
Node.js Question

Nested array query using mongoose

I'm using mongoose (with nodejs) to make my queries.

I got the following Schemas for my database model (minified ofc):

var HistorySchema = new Schema({
status : String,
time : Date
});

var TaskSchema = new Schema({
game_id : Schema.Types.ObjectId,
history : [HistorySchema]
}, {collection: 'task'});


Now I want to give an example (insertion) for this which I want to use to show my problem and wish:
(Insertion)

{
"_id" : ObjectId("5772ca87439632101510fa6b"),
"history" :
[
{
"status" : "open",
"time" : ISODate("2016-06-25T12:17:46.982Z")
},
{
"status" : "complete",
"time" : ISODate("2016-06-30T12:17:46.982Z")
}
]
}


so far so good... Now I have a given date, in this case:

ISODate("2016-06-28T12:17:46.982Z")


Now I want to get from my collection all TaskSchema objects including matching HistorySchmea objects from the array. So I want to exclude the not matching part in history array.

I tried many things like $pull operation like

db.task.find(
{
"game_id": ObjectId("57711397893a97aa170aa983"),
"history.time":{
$lte: ISODate("2016-06-28T12:17:46.982Z")
}
},{$pull: {
"history": {
time: {
$gte: ISODate("2016-06-28T12:17:46.982Z")
}
}
}
}


but then I get errors like

Unsupported projection option: $pull: { history: { time: { $gte: new Date(1467116266982) } } }


Does anyone know how I could realize this query? I'm working now for days on this and couldn't find any help.

Thanks in advance!

Answer

One solution is to use the aggregation framework :

db.task.aggregate([
    {$match: {_id : ObjectId("5772ca87439632101510fa6b")}},
    {$unwind : "$history"},
    {$match :{"history.time" : 
              {
                  $lte: ISODate("2016-06-30T12:17:46.982Z"),
                  $gte: ISODate("2016-06-01T12:17:46.982Z")
              }
             }
    },
    {$group:
     {
         _id:"$_id",
         history: { $push:  { status: "$history.status", time: "$history.time" } }
     }
    }
]);
Comments