Am I doing something wrong with aggregation pipeline?

I am using the following aggregation pipeline for expired vouchers

{
          $addFields: {
            status: {
              $cond: {
                if: {
                  $and: [
                    { startDate: { $lte: ['$startDate', new Date()] } },
                    { endDate: { $gte: ['$endDate', new Date()] } },
                  ],
                },
                then: 'Active',
                else: 'Expired',
              },
            },
          },
        },

This stage comes after $lookup stage, I have document schema like this

{
  _id: "A01",
   name: "History",
   startDate: "2021-03-26T21:00:00.000Z",
   endDate: "2021-04-29T21:00:00.000Z"
}

But I am getting active for all documents, despite the fact that a lot of my vouchers have been expired (see actual document above)

Any help will be appreciated

The following looks weird to me by the double presence of startDate and endDate.

I would try the alternative:

{ "$lte" : [ "$startDate" , new Date() ] },
{ "$gte" : [ "$endDate" , new Date() ] },

Thanks for your help, it works like a charm
At first I was following query operator way wrt $lte but I was getting the following error

Expression $lte takes exactly 2 arguments. 1 were passed in

I copied the answer from the following link,
Stack Overflow Answer

@steevej I recently added a new voucher with the following startDate and endDate

{
  _id: ObjectId(''')
  startDate: "2021-09-15T19:00:00.000Z",
  endDate: "2021-09-29T19:00:00.000Z",
}

But its returning Expired

The following gives you a correct expression:

But the logic is probably wrong. I think you want

Active if startDate <= new Date() <= endDate

If you simply plug the correct syntax in:

to

Then the only way to get Active would be if startDate === new Date() === endDate

Hi there @steevej, I followed the correct syntax before like this

if: {
                  $and: [
                    { $lte: ['$startDate', new Date()] },
                    { $gte: ['$endDate', new Date()] },
                  ],
                },
then: 'Active',
else: 'Expired',

But it returns Expired

@steevej
This is the logic that I am trying to implement

startDate <= currentTime && endDate >= currentTime

The solution was to use a predefined date string, I tried as follows

const date = new Date();
date.setDate(date.getDate());

const currentDate = date.toISOString();

if: {
      $and: [
             { $lte: ['$startDate', currentDate ] },
             { $gte: ['$endDate', currentDate ] },
             ],
     },
then: 'Active',
else: 'Expired',

And everything works flawlessly, the link that helped is the following
SO link

1 Like