MongoDB: subtract months from date, with value from database

Question

Is it possible in MongoDB to somehow subtract only months from a date, with a value from the same document. So for example i have to subtract date from field "valid_to" with months from field "offset", only months need to be changed. So for example:

1. 2019-11-25 - 3 months = 2019-08-25

2. 2019-12-20 - 5 months = 2019-07-20

Documents in collection:

    {
        "_id" : ObjectId("5d96f027ad768a1532aa2698"),
        "name" : "Q1",
            "valid_to" : ISODate("2019-11-25T14:36:32.221+01:00"),
        "offset" : 3,

    },
    {
        "_id" : ObjectId("5d96f027ad768a1532aa2699"),
        "name" : "Q2",
            "valid_to" : ISODate("2019-12-20T14:36:32.221+01:00"),  
            "offset" : 5,
    }

I tried just as an example using JavaScript date function and taking current date and subtracting months but it's not working like this

        {
            $project: {
                "subtractedDate": new Date(new Date().setMonth({ $subtract: [new Date().getMonth(), "$offset"] }))
            }
        },
    ])

As a result I am getting ISODate("1970-01-01T01:00:00.000+01:00")

Answer 1

Javascript functions do not work with aggregation framework. The whole aggregation is parsed to BSON format on input, so you can't call any JS inside. The reason is in converting BSON to C++ implementation.

In your example you are using mix of operators and JS, when you try to $subtract inside .setMonth():

$project: {
    "subtractedDate": new Date(new Date().setMonth({ $subtract: [new Date().getMonth(), "$offset"] }))
}

So JS doesn't know in advance how to handle Mongo operators and doesn't know about $offset value

Instead you have to use JS, that will be executed before input:

$project: {
    "subtractedDate": new Date(new Date().setMonth(new Date().getMonth() - 5))
}

That's the only solution to get you example work. But it won't help in your case since you don't know the exact offset.

What you can do is implement your logic using aggregation operators, there are lots of functions to work with Date like $dateToParts and $dateFromParts where you can easily manipulate with months, years and so on separately.

Or you can add a callback to your aggregation where you can handle all your dates:

db.collection.aggregate([
    { 
        // ...
    }
  ], function( err, results ) {
    if ( !err ) {
      results.forEach( result => {
        result.date = new Date(result.date)
          .setMonth(new Date(result.date).getMonth() - result.offset);
      });
    }
    callback(err, results);
  });
}