Query on date range with mongo

Question

My document looks like:

> db.users.find().pretty()
{
        "_id" : ObjectId("52ee0844177c86dc1d000001"),
        "profile" : {
                "displayName" : "Sydney",
        },
        "todos" : [
                {
                        "date" : ISODate("2014-02-02T21:47:13.064Z"),
                        "value" : "#first in the #middle also but twice #middle and the #end",
                        "tags" : [
                                "#first",
                                "#middle",
                                "#end"
                        ]
                },
                {
                        "date" : ISODate("2014-02-05T21:20:30.904Z"),
                        "value" : "Find mongo query #mongo #work",
                        "tags" : [
                                "#mongo",
                                "#work"
                        ]
                }
        ]
}

I want to query the todo for a date range:

> var start = new Date()
> var end = new Date()
> start.setDate(start.getDate() - 2)
1391544140700
> start
ISODate("2014-02-04T20:02:20.700Z")
> end
ISODate("2014-02-06T20:02:25.828Z")
>db.users.find({todos: {$elemMatch: {date: { $gte: start, $lt: end }}}}, {'todos':1}).pretty()

but the query still returns the two records. I was expecting to get only the last one.

I use the following answer to fix the issue: http://stackoverflow.com/a/12241733/1259510 — Sydney, Feb 06 '14 at 20:47

Neil Lunn · Answer 1 · 2014-02-06T22:30:59.000

From the documentation:

If multiple elements match the $elemMatch condition, the operator returns the first matching element in the array.

This is by design (limitation) and there is no other operator that would return only the matching elements. One approach is to use the aggregation framework to filter the returned elements of the array:

db.users.aggregate([
    {$match: {todos: {$elemMatch: {date: { $gte: start, $lt: end }} } }},
    {$unwind: "$todos"},
    {$match:{"todos.date": { $gte: start, $lt: end } }},
    {$group: { _id: { _id: "$_id", profile: "$profile" }, todos: {$push: "$todos" } } },
    {$project: {_id: "$_id._id", profile: "$_id.profile", todos: 1 } }
])

If only one field is required use the positional operator in combination with $elemMatch to return only the item that matched as it would not make much sense to restate the $elemMatch in the projection.

{todos: {$elemMatch: {date: { $gte: start, $lt: end }}}}, {"todos.$": 1}

I don't want the first, I want all items that matches. Using aggregate works — Sydney, Feb 06 '14 at 21:26
Using aggregate only will work in that case. Your example should be clearer to this point. As it stands you only have two documents. I believe you have answered your own question. — Neil Lunn, Feb 06 '14 at 21:32

Query on date range with mongo

1 Answers1