Find all elements in an array between two dates

Working with Data Node.js Frameworks
, , ,
1

My data set looks like this.

const userSchema = new mongoose.Schema({
    weekPlan: {
        type: [{
            title: { type: String, required: true },
            color: { type: String, required: true },
            recipeID: { type: mongoose.ObjectId, required: true },
            date: { type: Date, required: true }
        }]
    },
});

I try to get back from the array weekPlan only the elements that are between the two dates. However, I get either no result at all or all results unfiltered where it is clear that the date is not in the range.

I have tried the following two queries (js)

const query = User.findOne({
        _id: req.user,
        weekPlan: { $elemMatch: { date: { $gte: new Date(2023, 5, 10, 0, 0, 0), $lte: new Date() } } }
    },
        { _id: 0, weekPlan: 1 },
    )

// Seccond try
    const query = User.aggregate(
        { $match: { _id: req.user } },
        { $unwind: "$weekPlan" },
        {
            $match: {
                "weekPlan.dates": { $gte: new Date(2023, 5, 10, 0, 0, 0), $lte: new Date() }
            }
        }
    )
2

Your second query match looks wrong. Do the two checks in an $and block, one for gte and one for le etc.

3 
    const query = User.aggregate(
        { $match: { _id: req.user } },
        { $unwind: "$weekPlan" },
        {
            $match: {
                "weekPlan.dates": { $and: [{ $gte: new Date(2023, 5, 10, 0, 0, 0) }, { $lte: new Date() }] }
            }
        }
    )

No result with this changes

4

My personal account was limited to 8 posts a day and I seem to have blown that so replying on my work account but see below:

db.collection.aggregate([
  {
    $match: {
      $and: [
        {
          "type.date": {
            $gt: 0
          }
        },
        {
          "type.date": {
            $lt: 3
          }
        },
        
      ]
    }
  }
])
5

The explicit $and is not required. The following $match will return the same documents.

$match: { "type.date" : { $gt: 0 ,$lt: 3 } }

But I think the use-case is about only returning the matching element in the array for matching documents. I think that for this you need a $filter in a $project stage.

The $unwind version is also promising since an $unwind and $match is almost like a $filter.

1 Like
6

Ha, that’s something I’ve done forever, thanks for that reply! Probably as at some point in the past I added two filters against the same field and spend ages working out why only one of the conditions was picking up!
Shall be making use of that!

7

The $unwind is on the wrong field given the schema isn’t it? “type” is the array and not the weekPlan.