Querying a range of dates in Mongoose and Node

Question

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 for example. The query is returning all the data instead of returning the data for the days between the Date range.

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 }});
      }
    })

The solution that I found to work is:

let itsArray = Array.isArray(req.body.day)
    let first = itsArray ?  new Date(new Date(req.body.day[0]).toISOString()) : null
    let second = itsArray ? new Date(new Date(req.body.day[1]).toISOString()) : null
    let theDay = itsArray ? null : new Date(req.body.day)
    let now = new Date()
    theDay = itsArray ? null :  new Date(new Date(theDay).toISOString())

    const objectProject = { $project: {
        days: {
          $filter: {
            input: "$days",
            as: "index", 
            cond:{ $eq: [ "$$index.day", theDay ] },
          }
        }
    }}

    const arrayProject = { $project: {
        days: {
          $filter: {
            input: "$days",
            as: "index", 
            cond: {$and: [
              { $gte: [ "$$index.day", first ] },
              { $lte: [ "$$index.day", second ] }
            ]}
          }
        }
    }}

    await User.aggregate([
    {$match:{_id: ObjectID(getUserId(req))}},
    itsArray ? arrayProject : objectProject
  ])
  .project({'days.day':1, 'days.data':1})
  .then(result => {console.log(result})

Using project, match and then filter seems to yield the desired results.

Mr. Stefan · Accepted Answer · 2021-02-26 10:41:49Z

1

It seems that the result I wanted required some more advanced mongodb kung-fu:

const objectProject = { $project: {
        days: {
          $filter: {
            input: "$days",
            as: "index", 
            cond:{ $eq: [ "$$index.day", theDay ] },
          }
        }
    }}

    const arrayProject = { $project: {
        days: {
          $filter: {
            input: "$days",
            as: "index", 
            cond: {$and: [
              { $gte: [ "$$index.day", first ] },
              { $lte: [ "$$index.day", second ] }
            ]}
          }
        }
    }}

    await User.aggregate([
    {$match:{_id: ObjectID(getUserId(req))}},
    itsArray ? arrayProject : objectProject
  ])
  .project({'days.day':1, 'days.data':1})
  .then(result => {console.log(result})

answered Feb 26, 2021 at 10:41

Mr. Stefan

315 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying a range of dates in Mongoose and Node

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related