Search by Date within an Object in Array

Question

I have the following Mongoose schema:

let ExerciserSchema = new Schema({
  username: {
    type: String,
    required: true
  },
  exercises: [{
    desc: String,
    duration: Number,
    date: {
      type: Date,
      default: new Date()
    }
  }]
});

I want to search by username and limit the exercise results to a date range.

I tried this lookup function:

let user = await Exerciser.find(
      { "username": name },
      { "exercises.date": { "$gte": from }},
      { "exercises.date": { "$lte": to }}
    ).exec((err, data) => {
      if (err) {
        res.json({ Error: "Data not found" })
        return done(err);
      }
      else {
        res.json(data);
        return done(null, data);
      }
    });

However, it's logging an error and not returning the data. MongoError: Unsupported projection option: exercises.date: { $gte: new Date(1526342400000) }

I realize from that error it appears like my date is being searched for in milliseconds, but I console.log it right before I run the above function and it's in date mode, which is what I think I want: 2018-05-01T00:00:00.000Z

How can I make this work so that I can search by a date range given my Schema? I can change the format of the date in the Schema if necessary. I'd just like the simplest solution. Thanks for your help.

Neil Lunn · Answer 1 · 2018-05-15T22:11:27.630

You're query is wrong. You were trying to write an AND condition, but you separated documents instead of putting everything into one. This means the "second" argument to Model.find() was interpreted as a a "projection of fields", hence the error:

MongoError: Unsupported projection option:

So it's not a "schema problem" but that you sent the wrong arguments to the Model.find() method

Also you need $elemMatch for multiple conditions on elements within an array:

// Use a try..catch block with async/await of Promises
try {
  let user = await Exerciser.find({
    "username": name,
    "exercises": {
      "$elemMatch": { "date": { "$gte": from, "$lte": to } }
    }
  });
  // work with user
} catch(e) {
  // handle any errors
}

Most importantly you don't await a callback. You either await the Promise like I am showing here or simply pass in the callback instead. Not both.

Exerciser.find({
  "username": name,
  "exercises": {
    "$elemMatch": { "date": { "$gte": from, "$lte": to } }
  }
}).exec((err,user) => {
   // the rest
})

FYI, what you were attempting to do was this:

Exerciser.find({
  "$and": [
    { "username": name },
    { "exercises.date": { "$gte": from }},
    { "exercises.date": { "$lte": to }}
  ]
)

But that is actually still incorrect since without the $elemMatch the $gte and $lte applies to ALL elements of the array and not just a single one. So the incorrect results would show if ANY array item was less than the date but not necessarily greater than.

For array elements the $elemMatch enforces the "between" of the two conditions.

Hmmm. I'm getting results now, but it's not enforcing the `"$gte"` and `"$lte"`. I'm getting either all results or none. Trying to figure out what might be going on in the mean time, but any ideas? — Scott, May 15 '18 at 22:37
@Scott You're possibly looking for only the "matching array elements", which is different to the "matching documents". [Retrieve only the queried element in an object array in MongoDB collection](https://stackoverflow.com/q/3985214/2313887) addresses that different question. If you are unsure, then [Ask a New Question](https://stackoverflow.com/questions/ask) and show what you get as a result currently and what you expect to get. The worst that can happen is we point you at an existing answer, if you believe that one does not address what you are after. — Neil Lunn, May 16 '18 at 01:30

score 0 · Answer 2 · answered May 17 '18 at 20:33

I managed to get it. This answer matches by username, and filters exercises so they are between the dates with variable names to and from. This is what I had wanted.

    let user = Exerciser.aggregate([
    { $match: { "username": id }},
    { $project: { // $project passes along the documents with the requested fields to the next stage in the pipeline
      exercises: { $filter: {
        input: "$exercises",
        as: "exercise",
        cond: { $and: [
          { $lte: [ "$$exercise.date", to ] },
          { $gte: [ "$$exercise.date", from ] },
        ]}
      }},
      username: 1, // include username in returned data
      _id: 0
    }}
  ])

Result:

[
  {
    "username": "scott",
    "exercises": [
      {
        "desc": "Situps",
        "duration": 5,
        "_id": "5af4790fd9a9c80c11aac696",
        "date": "2018-04-30T00:00:00.000Z"
      },
      {
        "desc": "Situps",
        "duration": 10,
        "_id": "5afb3f03e12e38020d059e67",
        "date": "2018-05-01T00:00:00.000Z"
      },
      {
        "desc": "Pushups",
        "duration": 8,
        "_id": "5afc08aa9259ed008e7e0895",
        "date": "2018-05-02T00:00:00.000Z"
      }
    ]
  }
]

Search by Date within an Object in Array

2 Answers2