2

Using mongoose, I'm trying to make a query that searches for tasks where timeSpent is greater than timeBilled.

Task schema:

const myTaskSchema = new Schema({
  date: { type: Date, default: Date.now },
  timeSpent: { type: Number },
  timeBilled: { type: Number }
})

The query I've tried:

   myTaskSchema.aggregate([
      {
        $match: {
          timeSpent: { $gt: '$timeBilled' }
        }
      }
    ])
    .then(data => {
      console.log(data)
      })

But I'm getting zero results (I know there should be results)

NOTE: Not every task has a timeSpent or timeBilled.field if that matters.

Scott Barrar
  • 157
  • 1
  • 7
  • Does this answer your question? [MongoDb query condition on comparing 2 fields](https://stackoverflow.com/questions/4442453/mongodb-query-condition-on-comparing-2-fields) – ray Feb 11 '23 at 03:05
  • that thread lead me to a solution. I looks sloppy and I'd be open to other solutions but I'll post my solution here. – Scott Barrar Feb 11 '23 at 03:53

1 Answers1

1

here is my dirty solution. It'd be nice if I didnt have to add a field but this gets me where I want to be.

 myTaskSchema.aggregate([
      {
        $addFields: {
          needToBill: { $gt: ['$timeSpent', '$timeBilled'] }
        }
      },
      {
        $match: {
          needToBill: true
        }
      },
      {
        $project: {
          timeSpent: 1,
          timeBilled: 1
        }
      }
    
    ])
Scott Barrar
  • 157
  • 1
  • 7
  • You can do something simpler like [this](https://mongoplayground.net/p/XM4PnX1akyi) – ray Feb 11 '23 at 13:31