MongoDB return array with conditionals

Question

i need to perform a mongoDB query for get the status of a user between dates.

This is a document example:

{
    "_id" : ObjectId("538efd918163b19307c59e8e"),
    "username" : "admin",
    "hashedPassword" : "9qm4ufCPOCeU4AoBIMCJyf8oIvs8vWUYKIbImhc1N9HJrRKzCwCbeGNBsuOWnQ2kMbM635KmingRkn31wvHPQ/1bvs8Msg2rUchybaVvFmpy15A9Pnhb1BDHSLPftn0v8mSyhZQuOpP7Goirg07CGDPvJQqhN6oH6g0+iBTliAs=",
    "salt" : "EHLY4edLmrq7Nj4zRqh7Loow/O34pHNiWQ1i4vo8+SJTl82phMokAyTSr4MNKBeMMhJgCof1uK+3++PlXp5Y3I0SL3XkzJsSWG9K5oXJCE7B3j0Kazj4IlAllOEcXwMeuBVTnTgnBi9pUqdWyDysHG893oMVmr3VizycL/Iz484=",
    "realName" : "Administrator",
    "roles" : [ 
        "admin", 
        "operator", 
        "technician"
    ],
    "status" : [],
    "states" : [ 
        {
            "status" : "vacances",
            "date" : ISODate("2014-06-09T11:42:02.756Z")
        }, 
        {
            "status" : "busy",
            "date" : ISODate("2014-06-09T11:42:03.443Z")
        }, 
        {
            "status" : "eating",
            "date" : ISODate("2012-06-09T11:42:04.080Z")
        }, 
        {
            "status" : "busy",
            "date" : ISODate("2014-06-09T11:49:53.765Z")
        }, 
        {
            "status" : "vacances",
            "date" : ISODate("2014-06-09T11:49:54.402Z")
        }, 
        {
            "status" : "eating",
            "date" : ISODate("2014-06-09T11:49:56.409Z")
        }, 
        {
            "status" : "resting",
            "date" : ISODate("2014-06-09T11:49:57.114Z")
        }, 
        {
            "status" : "eating",
            "date" : ISODate("2014-06-09T12:24:38.880Z")
        }, 
        {
            "status" : "busy",
            "date" : ISODate("2014-06-09T14:51:13.440Z")
        }, 
        {
            "status" : "free",
            "date" : ISODate("2014-06-09T14:51:20.940Z")
        }, 
        {
            "status" : "vacances",
            "date" : ISODate("2014-06-12T11:05:36.448Z")
        }, 
        {
            "status" : "busy",
            "date" : ISODate("2014-06-12T11:05:37.425Z")
        }
    ]
}

Then, a want to get the status between a dates, i have this query:

var collection = db.collection('users');
    collection.aggregate([
        {$match: {$and: [
            { _id: new ObjectID(id)},
            { 'states.date': { $gt: new Date(from), $lte: new Date(to) }}
        ]}},
        {$unwind: "$states"},
        {$sort: {"states.date": -1}},
        {$group: {_id: "$_id", states: {$push: "$states"}}}
    ], function (e, doc) {
        if (e) {
            error(e);
        }
        success(doc);
    });

The result its wrong, because return me all states of the user, not only between dates query..How can i reduce the response in the mongodb query?

Answer 1

You should filter the documents after you $unwind the array. Your first match will match the entire document but will not filter out the documents in the array:

var collection = db.collection('users');
    collection.aggregate([
        {$match: { _id: new ObjectID(id) } }, 
        {$unwind: "$states"},
        {$match: { 'states.date': { $gt: new Date(from), $lte: new Date(to) }}},
        {$sort: {"states.date": -1}},
        {$group: {_id: "$_id", states: {$push: "$states"}}}
    ], function (e, doc) {
        if (e) {
            error(e);
        }
        success(doc);
    });

Edit

You also don't need to search for a date range in your first $match if you're looking for a specific user. Filtering only by _id will use a default index on that field and will be much faster.

Answer 2

Your first $match condition only matches documents and not array members. To "filter" you need to actually $match after you $unwind as well:

    collection.aggregate([
        {$match: {$and: [
            { _id: new ObjectID(id)},
            { 'states.date': { $gt: new Date(from), $lte: new Date(to) }}
        ]}},
        {$unwind: "$states"},
        {$match: {$and: [
            { 'states.date': { $gt: new Date(from), $lte: new Date(to) }}
        ]}},
        {$sort: {"states.date": -1}},
        {$group: {_id: "$_id", states: {$push: "$states"}}}
    ], function (e, doc) {
        if (e) {
            error(e);
        }
        success(doc);
    });