Query for Matching Dates within Array

Question

I have created a model schema with some nested fields in it, one of it is the Timestamp field:

{_id: Object_id,
  name: string,
someArray: [{Timestamp: Date, otherFields: ...},{Timestamp: Date, otherFields...},...],
...,
}

the Timestamp has of type: Timestamp: Date e.g (Timestamp: 2018-06-01T14:57:45.757647Z)

Now, I want to query only those documents from the array, which are between a start and end date that are received as parameters from a API url...

/link/Collection/:start.:end.:id

My router url (with the parameter strings as query) looks like this:

http://localhost:6000/link/Collection/2018-06-01T14:50:45.2018-06-01T15:17:45.29

My query function in mongoose / node (express) to retrieve the data looks like this:

exports.list_by_start_end_date_ID = function(req, res) {
    console.log(req.body)
    d_id=req.params.id;
    start = req.params.start;
    end = req.params.end;
    console.log(d_id);
    console.log(start)
    console.log(end)
    console.log(new Date(start));
    console.log(new Date(end));
    //SomeColl.findById(d_id, "myCollection").where("myCollection.Timestamp").gte(new Date(start)).lte(new Date(end))
    SomeColl.findById(d_id, "myCollection",{"myCollection.Timestamp": {"$gte": new Date(start), "$lte": new Date(end)}})
    .exec( function(err, fps) {
        if (err)
            res.send(err);
        res.json(fps);
    });
};

I get returned:

[{"Timestamp":"2018-06-01T14:57:45.757647Z"...},{"Timestamp":"2018-06-01T15:27:45.757647Z"...},{"Timestamp":"2018-06-01T15:12:45.757647Z"...}]

I don't get any error, I also can create new Date(start) from start and end parameters and it's correct, but as you can see, the document with 15:27 time shouldn't be returned...

I tried out both versions (also commented out version) of the query strings, and I also tried with the blank ISO Date format string that I passed as parameter (start / end) to the url.. but neither worked. How can I compare the dates in mongoose and get the correct documents passed back?

EDIT: I tried to find a workaround by ignoring db api operations, and just parsing the correct documents (subdocuments) of the array with javascript..:

myColl.findById(d_id)
    .exec( function(err, fps) {
        if (err) {
        console.log(err);
            res.send(err);
        }
        else {          
            //console.log(fps["someArray"])
            laenge = fps["someArray"].length;
            console.log(laenge);
            startts = +new Date(start)
            endts = +new Date(end)
            TSarray = []
            console.log(startts,endts)
            for (let doc of fps["someArray"]) {
                ts = +new Date(doc["Timestamp"])
                //console.log(doc)
                if ((ts >= startts) && (ts <= endts)){
                    TSarray.push(doc)   
                    //console.log(TSarray)                              
                }
            }       
            res.json(TSarray)   
        //res.json(fps);
    }
    })//.then((res) => res.json())
};

However, when I want to get the results from the array, I get HTTP 304 error.. I did not find out yet, how to retrieve the corresponding subdocuments (based on a filter criteria) of an array field of one single document.. Do I have to use projection to get only the array field, and then use some filter criteria on that array to get the right subdocuments, or how does it generally work?

//EDIT2: I tried with the mongoDB aggregation framework, but get returned []:

myColl.aggregate([{$match: {"id":d_id},
            someArray: {
                $filter: {
                    input: "$someArray",
                    as: "fp",
                    cond: {$and: [
                        {$gte: [ "$$fp.Timestamp", new Date(start)]},
                        {$lte: [ "$$fp.Timestamp", new Date(end)]}
                    ]}

                }
            }
        }
    }]).exec( function(err, fps) {
        if (err) {
        console.log(err);
            res.send(err);
        }
        else {  
            console.log(fps)
            res.json(fps);
    }
    })}
;

This also does not work, is there anything wrong with that query? How can I specify a date range in mongoose with the filter criteria condition?

//EDIT3: After 5 days of work, I finally managed to get the right documents returned, based on a timestamp. However, to get documents from 14:00:00 o'clock, I have to enter 16:00:00 as url parameter... I know it probably has something to do with UTC and timezones... my tz is Berlin, so I think its UTC +2 as MongoDB servers are in NY I think... How can I best accomodate to that problem?

Here is my function:

myColl.findById(d_id, "someArray")
    .exec( function(err, fps) {
        if (err) {
        console.log(err);
            res.send(err);
        }
        else {  
            startts = +new Date(start)
            endts = +new Date(end)
            TSarray = []
            for (let doc of fps["Fahrplanabschnitte"]) {
                ts = + new Date(doc["Timestamp"]                    
                if ((ts >= startts) && (ts <= endts)){
                    TSarray.push(doc)   

                }               
            }   
            //for (let a of TSarray) {console.log(a)};
            res.json(TSarray);
        }
    })
};

Answer 1

You're missing the $elemMatch operator on the basic query and the $filter you attempted with the aggregation framework actually has incorrect syntax.

So returning the document matching the dates being within that range in the array is:

// Simulating the date values
var start = new Date("2018-06-01"); // otherwise new Date(req.params.start)
var end = new Date("2018-07-01");   // otherwise new Date(req.params.end)

myColl.find({ 
  "_id": req.params.id,
  "someArray": {
    "$elemMatch": {  "$gte": start, "$lt": end  }
  }
}).then( doc => {
  // do something with matched document
}).catch(e => { console.err(e); res.send(e); })

Filtering the actual array elements to be returned is:

// Simulating the date values
var start = new Date("2018-06-01");
var end = new Date("2018-07-01");

myColl.aggregate([
  { "$match": { 
    "_id": mongoose.Types.ObjectId(req.params.id),
    "someArray": {
      "$elemMatch": { "$gte": start, "$lt": end }
    }
  }},
  { "$project": {
    "name": 1,
    "someArray": {
      "$filter": {
        "input": "$someArray",
        "cond": {
          "$and": [
            { "$gte": [ "$$this.Timestamp", start ] }
            { "$lt": [ "$$this.Timestamp", end ] }
          ]
        }
      }
    }
  }}
]).then( docs => {
  /* remember aggregate returns an array always, so if you expect only one
   * then it's index 0
   *
   * But now the only items in 'someArray` are the matching ones, so you don't need 
   * the code you were writing to just pull out the matching ones
   */
   console.log(docs[0].someArray);
  
}).catch(e => { console.err(e); res.send(e); })

The things to be aware of are that in the aggregate() you need to actually "cast" the ObjectId value, because Mongoose "autocasting" does not work here. Normally mongoose reads from the schema to determine how to cast the data, but since aggregation pipelines "change things" then this does not happen.

The $elemMatch is there because as the documentation says:

When specifying conditions on more than one field nested in an array of documents, you can specify the query such that either a single document meets these condition or any combination of documents (including a single document) in the array meets the conditions.

Use $elemMatch operator to specify multiple criteria on an array of embedded documents such that at least one embedded document satisfies all the specified criteria.

In short $gte and $lt are an AND condition and count as "two", therefore the simple "dot notation" form does not apply. It's also $lt and not $lte, since it makes more sense to be "less than" the "next day" rather than looking for equality up to the "last millisecond".

The $filter of course does exactly what it's name suggests and "filters" the actual array content so that only matching items are left behind.

---

Demonstration

Full demonstration listing creates two documents, one having only two array items which actually match the date range. The first query shows the correct document is matched with the range. The second shows the "filtering" of the array:

const { Schema, Types: { ObjectId } } = mongoose = require('mongoose');

const uri = 'mongodb://localhost/test';

mongoose.Promise = global.Promise;
mongoose.set('debug',true);

const subSchema = new Schema({
  timestamp: Date,
  other: String
});

const testSchema = new Schema({
  name: String,
  someArray: [subSchema]
});

const Test = mongoose.model('Test', testSchema, 'filtertest');

const log = data => console.log(JSON.stringify(data, undefined, 2));

const startDate = new Date("2018-06-01");
const endDate = new Date("2018-07-01");

(function() {

  mongoose.connect(uri)
    .then(conn =>
      Promise.all(Object.entries(conn.models).map(([k,m]) => m.remove()))
    )
    .then(() =>
      Test.insertMany([
        {
          _id: "5b1522f5cdac0b6da18f7618",
          name: 'A',
          someArray: [
            { timestamp: new Date("2018-06-01"), other: "C" },
            { timestamp: new Date("2018-07-04"), other: "D" },
            { timestamp: new Date("2018-06-10"), other: "E" }
          ]
        },
        {
          _id: "5b1522f5cdac0b6da18f761c",
          name: 'B',
          someArray: [
            { timestamp: new Date("2018-07-04"), other: "D" },
          ]
        }
      ])
    )
    .then(() =>
      Test.find({
        "someArray": {
          "$elemMatch": {
            "timestamp": { "$gte": startDate, "$lt": endDate }
          }
        }
      }).then(docs => log({ docs }))
    )
    .then(() =>
      Test.aggregate([
        { "$match": {
          "_id": ObjectId("5b1522f5cdac0b6da18f7618"),
          "someArray": {
            "$elemMatch": {
              "timestamp": { "$gte": startDate, "$lt": endDate }
            }
          }
        }},
        { "$addFields": {
          "someArray": {
            "$filter": {
              "input": "$someArray",
              "cond": {
                "$and": [
                  { "$gte": [ "$$this.timestamp", startDate ] },
                  { "$lt": [ "$$this.timestamp", endDate ] }
                ]
              }
            }
          }
        }}
      ]).then( filtered => log({ filtered }))
    )
    .catch(e => console.error(e))
    .then(() => mongoose.disconnect());

})()

Or a bit more modern with async/await syntax:

const { Schema, Types: { ObjectId } } = mongoose = require('mongoose');

const uri = 'mongodb://localhost/test';

mongoose.Promise = global.Promise;
mongoose.set('debug',true);

const subSchema = new Schema({
  timestamp: Date,
  other: String
});

const testSchema = new Schema({
  name: String,
  someArray: [subSchema]
});

const Test = mongoose.model('Test', testSchema, 'filtertest');

const log = data => console.log(JSON.stringify(data, undefined, 2));

(async function() {

  try {

    const startDate = new Date("2018-06-01");
    const endDate = new Date("2018-07-01");

    const conn = await mongoose.connect(uri);

    // Clean collections
    await Promise.all(Object.entries(conn.models).map(([k,m]) => m.remove()));

    // Create test items

    await Test.insertMany([
      {
        _id: "5b1522f5cdac0b6da18f7618",
        name: 'A',
        someArray: [
          { timestamp: new Date("2018-06-01"), other: "C" },
          { timestamp: new Date("2018-07-04"), other: "D" },
          { timestamp: new Date("2018-06-10"), other: "E" }
        ]
      },
      {
        _id: "5b1522f5cdac0b6da18f761c",
        name: 'B',
        someArray: [
          { timestamp: new Date("2018-07-04"), other: "D" },
        ]
      }
    ]);



    // Select matching 'documents'
    let docs = await Test.find({
      "someArray": {
        "$elemMatch": {
          "timestamp": { "$gte": startDate, "$lt": endDate }
        }
      }
    });
    log({ docs });

    let filtered = await Test.aggregate([
      { "$match": {
        "_id": ObjectId("5b1522f5cdac0b6da18f7618"),
        "someArray": {
          "$elemMatch": {
            "timestamp": { "$gte": startDate, "$lt": endDate }
          }
        }
      }},
      { "$addFields": {
        "someArray": {
          "$filter": {
            "input": "$someArray",
            "cond": {
              "$and": [
                { "$gte": [ "$$this.timestamp", startDate ] },
                { "$lt": [ "$$this.timestamp", endDate ] }
              ]
            }
          }
        }
      }}
    ]);
    log({ filtered });

    mongoose.disconnect();

  } catch(e) {
    console.error(e)
  } finally {
    process.exit()
  }

})()

Both are the same and give the same output:

Mongoose: filtertest.remove({}, {})
Mongoose: filtertest.insertMany([ { _id: 5b1522f5cdac0b6da18f7618, name: 'A', someArray: [ { _id: 5b1526952794447083ababf6, timestamp: 2018-06-01T00:00:00.000Z, other: 'C' }, { _id: 5b1526952794447083ababf5, timestamp: 2018-07-04T00:00:00.000Z, other: 'D' }, { _id: 5b1526952794447083ababf4, timestamp: 2018-06-10T00:00:00.000Z, other: 'E' } ], __v: 0 }, { _id: 5b1522f5cdac0b6da18f761c, name: 'B', someArray: [ { _id: 5b1526952794447083ababf8, timestamp: 2018-07-04T00:00:00.000Z, other: 'D' } ], __v: 0 } ], {})
Mongoose: filtertest.find({ someArray: { '$elemMatch': { timestamp: { '$gte': new Date("Fri, 01 Jun 2018 00:00:00 GMT"), '$lt': new Date("Sun, 01 Jul 2018 00:00:00 GMT") } } } }, { fields: {} })
{
  "docs": [
    {
      "_id": "5b1522f5cdac0b6da18f7618",
      "name": "A",
      "someArray": [
        {
          "_id": "5b1526952794447083ababf6",
          "timestamp": "2018-06-01T00:00:00.000Z",
          "other": "C"
        },
        {
          "_id": "5b1526952794447083ababf5",
          "timestamp": "2018-07-04T00:00:00.000Z",
          "other": "D"
        },
        {
          "_id": "5b1526952794447083ababf4",
          "timestamp": "2018-06-10T00:00:00.000Z",
          "other": "E"
        }
      ],
      "__v": 0
    }
  ]
}
Mongoose: filtertest.aggregate([ { '$match': { _id: 5b1522f5cdac0b6da18f7618, someArray: { '$elemMatch': { timestamp: { '$gte': 2018-06-01T00:00:00.000Z, '$lt': 2018-07-01T00:00:00.000Z } } } } }, { '$addFields': { someArray: { '$filter': { input: '$someArray', cond: { '$and': [ { '$gte': [ '$$this.timestamp', 2018-06-01T00:00:00.000Z ] }, { '$lt': [ '$$this.timestamp', 2018-07-01T00:00:00.000Z ] } ] } } } } } ], {})
{
  "filtered": [
    {
      "_id": "5b1522f5cdac0b6da18f7618",
      "name": "A",
      "someArray": [
        {
          "_id": "5b1526952794447083ababf6",
          "timestamp": "2018-06-01T00:00:00.000Z",
          "other": "C"
        },
        {
          "_id": "5b1526952794447083ababf4",
          "timestamp": "2018-06-10T00:00:00.000Z",
          "other": "E"
        }
      ],
      "__v": 0
    }
  ]
}