MongoDB/Mongoose - Querying an array of objects by date

Question

I have the following metrics collection:

{
    name: "Hello",
    values: [
        {
            value: 2629,
            date: "2016-10-28T07:00:00.000Z",
            _id: "58453abfef7aaa15ac1fdee8"
        },
        {
            value: 1568,
            date: "2016-10-29T07:00:00.000Z",
            _id: "58453abfef7aaa15ac1fdee7"
        },
        {
            value: 1547,
            date: "2016-10-30T07:00:00.000Z",
            _id: "58453abfef7aaa15ac1fdee6"
        },
        {
            value: 1497,
            date: "2016-10-31T07:00:00.000Z",
            _id: "58453abfef7aaa15ac1fdee5"
        },
        {
            value: 3031,
            date: "2016-11-01T07:00:00.000Z",
            _id: "58453abfef7aaa15ac1fdee4"
        },
        {
            value: 2559,
            date: "2016-11-02T07:00:00.000Z",
            _id: "58453abfef7aaa15ac1fdee3"
        },
        {
            value: 2341,
            date: "2016-11-03T07:00:00.000Z",
            _id: "58453abfef7aaa15ac1fdee2"
        },
        {
            value: 2188,
            date: "2016-11-04T07:00:00.000Z",
            _id: "58453abfef7aaa15ac1fdee1"
        },
        {
            value: 3280,
            date: "2016-11-05T07:00:00.000Z",
            _id: "58453abfef7aaa15ac1fdee0"
        },
        {
            value: 4638,
            date: "2016-11-06T07:00:00.000Z",
            _id: "58453abfef7aaa15ac1fdedf"
        }
    ]
},
.... more of the same

What I would like to get is all the values between a custom date range. I've tried the following query but I still get the entire values array returned:

{
    name: "Hello", 
    values: {
        $elemMatch: {
            date: {
                $lt: "2016-11-03T07:00:00.000Z", 
                $gt: "2016-10-28T07:00:00.000Z" 
            }
        }
    }
}

Maybe I saved my dates in a wrong format ? Any help would be greatly appreciated.

chridam · Accepted Answer · 2016-12-05 12:15:16Z

6

You can run an aggregation pipeline that uses the $filter operator on the values array. The following mongo shell query demonstrates this:

var start = new Date("2016-10-28T07:00:00.000Z"),
    end = new Date("2016-11-03T07:00:00.000Z");

db.metrics.aggregate([
    { 
        "$match": { 
            "name": "Hello",
            "values.date": { "$gt": start, "$lt": end }
        } 
    },
    {
        "$project": {
            "name": 1,
            "values": {
                "$filter": {
                    "input": "$values",
                    "as": "value",
                    "cond": { 
                        "$and": [
                            { "$gt": [ "$$value.date", start ] },
                            { "$lt": [ "$$value.date", end ] }
                        ]
                    }
                }
            }
        }
    }
])

Sample Output

/* 1 */
{
    "_id" : ObjectId("5845453145fda1298fa50db9"),
    "name" : "Hello",
    "values" : [ 
        {
            "value" : 1568,
            "date" : ISODate("2016-10-29T07:00:00.000Z"),
            "_id" : ObjectId("58453abfef7aaa15ac1fdee7")
        }, 
        {
            "value" : 1547,
            "date" : ISODate("2016-10-30T07:00:00.000Z"),
            "_id" : ObjectId("58453abfef7aaa15ac1fdee6")
        }, 
        {
            "value" : 1497,
            "date" : ISODate("2016-10-31T07:00:00.000Z"),
            "_id" : ObjectId("58453abfef7aaa15ac1fdee5")
        }, 
        {
            "value" : 3031,
            "date" : ISODate("2016-11-01T07:00:00.000Z"),
            "_id" : ObjectId("58453abfef7aaa15ac1fdee4")
        }, 
        {
            "value" : 2559,
            "date" : ISODate("2016-11-02T07:00:00.000Z"),
            "_id" : ObjectId("58453abfef7aaa15ac1fdee3")
        }
    ]
}

For MongoDB 3.0, the following workaround applies:

var start = new Date("2016-10-28T07:00:00.000Z"),
    end = new Date("2016-11-03T07:00:00.000Z");

db.metrics.aggregate([
    { 
        "$match": { 
            "name": "Hello",
            "values.date": { "$gt": start, "$lt": end }
        } 
    },
    {
        "$project": {
            "name": 1,
            "values": {
                "$setDifference": [
                    {
                        "$map": {
                            "input": "$values",
                            "as": "value",
                            "in": {
                                "$cond": [
                                    { 
                                        "$and": [
                                            { "$gt": [ "$$value.date", start ] },
                                            { "$lt": [ "$$value.date", end ] }
                                        ]
                                    },
                                    "$$value",
                                    false
                                ]
                            }
                        }
                    },
                    [false]
                ]
            }
        }
    }
])

edited Dec 5, 2016 at 12:15

answered Dec 5, 2016 at 10:46

chridam

104k26 gold badges246 silver badges243 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

aroundsix Over a year ago

Hi, I'm getting the following error: "errmsg" : "exception: invalid operator '$filter'", "code" : 15999, "ok" : 0

chridam Over a year ago

What is your MongoDB server version?

aroundsix Over a year ago

Current version: 3.0.12

chridam Over a year ago

In that case the above won't work unless you bump your version to 3.2. Let me update my answer with a workaround.

aroundsix Over a year ago

Wow that works. Thank you so much. I haven't been studying Mongo for long so things aren't still as clear as they should be. Really appreciate the help.

Mohsen ZareZardeyni · Accepted Answer · 2016-12-05 11:30:23Z

0

The Aggregation Framework in MongoDB 2.2+ provides an alternative to Map/Reduce. The $unwind operator can be used to separate your values array into a stream of documents that can be matched:

db.tmp.aggregate(
    // Start with a $match pipeline which can take advantage of an index and limit documents processed
    { $match : {
        name: "Hello",
        "values.date": {
            $lt: "2016-11-03T07:00:00.000Z",
            $gt: "2016-10-28T07:00:00.000Z" }
    }},
    { $unwind : "$values" },
    { $match : {
        name: "Hello",
        "values.date": {
            $lt: "2016-11-03T07:00:00.000Z",
            $gt: "2016-10-28T07:00:00.000Z" }
    }}
)

Sample output:

{
"_id":ObjectId("5845432720ce37bdc7e9ca1c"),
   "name":"Hello",
   "values":{  
      "value":1568,
      "date":"2016-10-29T07:00:00.000Z",
      "_id":"58453abfef7aaa15ac1fdee7"
   }
},{  
   "_id":ObjectId("5845432720ce37bdc7e9ca1c"),
   "name":"Hello",
   "values":{  
      "value":1547,
      "date":"2016-10-30T07:00:00.000Z",
      "_id":"58453abfef7aaa15ac1fdee6"
   }
},{  
   "_id":ObjectId("5845432720ce37bdc7e9ca1c"),
   "name":"Hello",
   "values":{  
      "value":1497,
      "date":"2016-10-31T07:00:00.000Z",
      "_id":"58453abfef7aaa15ac1fdee5"
   }
},{  
   "_id":ObjectId("5845432720ce37bdc7e9ca1c"),
   "name":"Hello",
   "values":{  
      "value":3031,
      "date":"2016-11-01T07:00:00.000Z",
      "_id":"58453abfef7aaa15ac1fdee4"
   }
},{  
   "_id":ObjectId("5845432720ce37bdc7e9ca1c"),
   "name":"Hello",
   "values":{  
      "value":2559,
      "date":"2016-11-02T07:00:00.000Z",
      "_id":"58453abfef7aaa15ac1fdee3"
   }
}

edited Dec 5, 2016 at 11:30

answered Dec 5, 2016 at 10:24

Mohsen ZareZardeyni

9568 silver badges17 bronze badges

3 Comments

aroundsix Over a year ago

Hi. That's just returning one value now. I'd like to get all the values between 2016-10-28 and 2016-11-03.

Mohsen ZareZardeyni Over a year ago

check it again!

aroundsix Over a year ago

it now returns just an empty array.

Collectives™ on Stack Overflow

MongoDB/Mongoose - Querying an array of objects by date

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related