How to range query a date field within an array of nested documents?

Hello

I have the following User schema:

    const User = mongoose.model(
      "User",
      new mongoose.Schema({
        email: String,
        password: String,
        name: String,
        days: [
          {
          day: Date,
          data:  
            { 
              average_score: {type: mongoose.Schema.Types.Decimal128, default: 0 }
            }
          }
        ]
      })
    );

In the day field I’m storing the days in ISO format like 2020-12-29T00:00:00.000Z. The User.find query is returning all the days instead of returning the data for the days between the Date range and I’m not sure why is this happening.

    User.find({ "_id": getUserId(req), "days.day":{
            "$gte": new Date("2021-01-02T00:00:00.000Z"), 
            "$lt": new Date("2021-01-04T00:00:00.000Z")
        }},
        function (err, result) {
          if (err){
            res.status(400).send({data: { message: err }});
            return;
          }
          else if(result)
          {
            res.status(200).send({data: { message: result }});
          }
        })

Hello @Stefan_Tesoi, welcome to the MongoDB Community Forum!

Your query looks just about correct as per this documentation example: Query an Array of Embedded Documents - A Single Nested Document Meets Multiple Query Conditions on Nested Fields.

You can use projection : Project Specific Array Elements in the Returned Array. The documentation also says:

See the aggregation operator $filter to return an array with only those elements that match the specified condition.

Here are posts with similar question and use $filter:

2 Likes

Thank you for pointing me to the right solution.

User.aggregate([
    {$match:{_id: ObjectID("5feb7b1b5438fcda7401f306")}},
    { $project: {
        days: {
          $filter: {
            input: "$days", // le tableau à limiter 
            as: "index", // un alias
            cond: {$and: [
              { $gte: [ "$$index.day", new Date("2020-12-29T00:00:00.000Z") ] },
              { $lte: [ "$$index.day", new Date("2020-12-31T00:00:00.000Z") ] }
            ]}
          }
        }
    }}
  ])
  .project({'days.day':1, 'days.data':1})
  .then(result => { res.status(200).send({data: { message: result }})})
1 Like