How to filter data from mongodb that the difference between (date years (these both from database)-CodePudding

there is a schema

const schemaDB = mongoose.Schema({
  product:{
    type: String,
  },
  DateOfInstallation: {
    type: Date
  },
  standardWarranty: {
    type: Number
  },
  extendedWarranty: {
    type: Number
  },
  AMC:{
    type: Number
  }
})

i need to get the list of all products that are going to expire within 1 month from today.

total_years_to_Add = standardWarranty    extendedWarranty   AMC
date_exp = DateOfInstallation   addYears(total_years_to_Add) 
date_diff = date_exp - today's Date

return data if date_diff less than month

plz help in debug the given mongodb aggregate query (implemented in mongoose)

const datas = await DBModel.aggregate([
{
$match: {
total: { $add: ['$standardWarranty','$extendedWarranty','$AMC']},
newdate: { $dateAdd: {
startDate: '$DateOfInstallation',
unit: "year",
amount: "$$total"
}},
$lte: [{$dateDiff:{
startDate: "$$newdate",
endDate: new Date(),
unit: "month"
}},1]
}},
{ $project:{
"DateOfInstallation":1, "standardWarranty":1, "extendedWarranty":1,"AMC":1
}},
{$sort:{
"DateOfInstallation": 1
}}
])

i know that $add cannot be used inside $match, can anyone help to query the same

CodePudding user response：

You are into the right direction. However you must use $expr:

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $lte: [
          {
            $dateDiff: {
              startDate: {
                $dateAdd: {
                  startDate: "$DateOfInstallation",
                  unit: "year",
                  amount: {
                    $add: [
                      "$standardWarranty",
                      "$extendedWarranty",
                      "$AMC"
                    ]
                  }
                }
              },
              endDate: new Date(),
              unit: "month"
            }
          }, 1
        ]
      }
    }
  },
  {
    $project: {
      "DateOfInstallation": 1,
      "standardWarranty": 1,
      "extendedWarranty": 1,
      "AMC": 1
    }
  },
  { $sort: { "DateOfInstallation": 1 } }
])

or maybe a bit easier to understand:

db.collection.aggregate([
  {
    $set: {
      total: {
        $add: [
          "$standardWarranty",
          "$extendedWarranty",
          "$AMC"
        ]
      }
    }
  },
  {
    $set: {
      newdate: {
        $dateAdd: {
          startDate: "$DateOfInstallation",
          unit: "year",
          amount: "$total"
        }
      }
    }
  },
  {
    $set: {
      dateDiff: {
        $dateDiff: {
          startDate: "$newdate",
          endDate: new Date(),
          unit: "month"
        }
      }
    }
  },
  { $match: { "$dateDiff": { $lt: 1 } } },
  {
    $project: {
      "DateOfInstallation": 1,
      "standardWarranty": 1,
      "extendedWarranty": 1,
      "AMC": 1
    }
  },
  { $sort: { "DateOfInstallation": 1 } }
])