Getting data from subdocument between dates

Question

I'm trying to get a certain amount of historical data from my documents similar to the SQL's Between clause. I searched for similar questions and managed to find this query but it only returns the first element of the array, so it's not enough:

db.docs.find({ _id: "eraj4983tj3" }, {history: { $elemMatch : {time: {$gt: new ISODate("2016-03-21T20:53:33.662Z") , $lt: new ISODate("2016-03-21T20:54:20.313Z") } } } });

My documents look like this:

{
    "_id": "eraj4983tj3",
    "descr": "somestuff",
    "history": [
        {
            "time": ISODate("2016-03-21T20:52:31.389Z"),
            "value": 103.91
        },
        {
            "time": ISODate("2016-03-21T20:53:33.663Z"),
            "value": 106.91
        },
        {
            "time": ISODate("2016-03-21T20:53:45.179Z"),
            "value": 104.91
        },
        {
            "time": ISODate("2016-03-21T20:54:20.313Z"),
            "value": 105.11
        },
        {
            "time": ISODate("2016-03-21T20:54:53.649Z"),
            "value": 105.41
        },
        {
            "time": ISODate("2016-03-21T20:55:12.998Z"),
            "value": 115.91
        }
    ]
}

And the result of my query should return this:

{
    "_id": "eraj4983tj3",
    "history": [
        {
            "time": ISODate("2016-03-21T20:53:45.179Z"),
            "value": 104.91
        },
        {
            "time": ISODate("2016-03-21T20:54:20.313Z"),
            "value": 105.11
        },
        {
            "time": ISODate("2016-03-21T20:54:53.649Z"),
            "value": 105.41
        }
    ]
}

How should i write my query to achieve this result?

You can write your queries as you want: Arrays do not have a guaranteed order. Your data modeling is plainly wrong. — Markus W Mahlberg, Apr 06 '16 at 21:17
I'm not asking about the order, i just need the data between two dates. — damnputer, Apr 06 '16 at 21:23
See below. Plus, it is undefined which of the potential documents this would be. — Markus W Mahlberg, Apr 06 '16 at 21:26
What do you mean it's undefined? I need the data from a document which id i have specified in the query. — damnputer, Apr 06 '16 at 21:29
Undefined == can be in any, arbitrary order. Take `[a,b,c,d]`. Depending on a number of factors, it may be returned this way or as `[b,a,d,c]` or any other order. — Markus W Mahlberg, Apr 06 '16 at 22:16
This question is _not_ a duplicate of http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection, since a range of matching elements are to be returned, although the update on the accepted answer could work for that. — Markus W Mahlberg, Apr 07 '16 at 18:38
I agree, that previous question doesn't cover the problems i had with my query. — damnputer, Apr 07 '16 at 21:07

score 4 · Accepted Answer · answered Apr 06 '16 at 22:46

What you want is not possible with a simple query. However, you can use an aggregation:

db.yourColl.aggregate([
    { $match:{ _id:"eraj4983tj3" } },
    { $unwind: "$history" },
    { $match:{ "history.time":{
        $gt: new ISODate("2016-03-21T20:53:33.662Z") ,
        $lt: new ISODate("2016-03-21T20:54:20.313Z")
    }}},
    { $group:{ _id:"$_id", history:{ $push:"$history" }}}
])

Which gives you the (correct) result of

{
  "_id" : "eraj4983tj3",
  "history" : [
      { "time" : ISODate("2016-03-21T20:53:33.663Z"), "value" : 106.91 },
      { "time" : ISODate("2016-03-21T20:53:45.179Z"), "value" : 104.91 }
  ]
}

Now, let us examine the stages:

{ $match:{ _id:"eraj4983tj3" } }: Find the correct doc

{ $unwind: "$history" } Create a new document for each array item and send it down the pipeline. The output of this stage looks like this:

{ "_id" : "eraj4983tj3", "descr" : "somestuff", "history" : { "time" : ISODate("2016-03-21T20:52:31.389Z"), "value" : 103.91 } }
{ "_id" : "eraj4983tj3", "descr" : "somestuff", "history" : { "time" : ISODate("2016-03-21T20:53:33.663Z"), "value" : 106.91 } }
{ "_id" : "eraj4983tj3", "descr" : "somestuff", "history" : { "time" : ISODate("2016-03-21T20:53:45.179Z"), "value" : 104.91 } }
{ "_id" : "eraj4983tj3", "descr" : "somestuff", "history" : { "time" : ISODate("2016-03-21T20:54:20.313Z"), "value" : 105.11 } }
{ "_id" : "eraj4983tj3", "descr" : "somestuff", "history" : { "time" : ISODate("2016-03-21T20:54:53.649Z"), "value" : 105.41 } }
{ "_id" : "eraj4983tj3", "descr" : "somestuff", "history" : { "time" : ISODate("2016-03-21T20:55:12.998Z"), "value" : 115.91 } }

{ $match:{ "history.time":{ $gt: new ISODate("2016-03-21T20:53:33.662Z"), $lt: new ISODate("2016-03-21T20:54:20.313Z")}}} We match again to find the history items from above stage that match our date range. The output of this stage looks like this:

{ "_id" : "eraj4983tj3", "descr" : "somestuff", "history" : { "time" : ISODate("2016-03-21T20:53:33.663Z"), "value" : 106.91 } }
{ "_id" : "eraj4983tj3", "descr" : "somestuff", "history" : { "time" : ISODate("2016-03-21T20:53:45.179Z"), "value" : 104.91 } }

{ $group:{ _id:"$_id", history:{ $push:"$history" }}} We group by _id (nothing new here), and for each document of the output of above stage, we $push its value for history (an object) to the array history in the newly created grouped document.

score 1 · Answer 2 · answered Apr 06 '16 at 21:18

1

The positional operator $ and the elemMatch will only return one sub document.

answered Apr 06 '16 at 21:18

BanksySan

27,362
33
117
216

That query returns just one time-value pair like this: { "_id" : "eraj4983tj3", "history" : [ { "time" : ISODate("2016-03-21T20:53:33.663Z"), "value" : 106.91 } ] } no matter how large period i leave between $gt and $lt operators. I need to receive all entries between the dates, not just one. – damnputer Apr 06 '16 at 21:26
Sorry, those operators only return one. – BanksySan Apr 06 '16 at 21:27

Getting data from subdocument between dates

2 Answers2