I have the following DB structure :

{
    "uploadedAt": "2021-09-22T22:09:12.133Z",
    "paidAt: "2021-09-30T22:09:12.133Z",
    "amount": {
        "currency": "EUR",
        "expected": 70253,
        "paid": 0
    },
}

I would like to know how do I calculate the total amount that still need to be paid (expected - paid), and the average date between uploadedAt and paidAt. This for multiple records.

My function for getting the data is (the criteria should be updated to get this data).

const invoiceParams = new FindParams();
invoiceParams.criteria = { company: company._id }

const invoices = await this.findAll(invoiceParams);

FindAll function looks like:

  async findAll(
    params: FindParams,
    ability?: Ability,
    includeDeleted: boolean = false,
  ): Promise<Entity[]> {
    let queryCriteria: Criteria = params.criteria;
    let query: DocumentQuery<Entity[], Entity> = null;

    if (!includeDeleted) {
      queryCriteria = {
        ...queryCriteria,
        deleted: { $ne: true },
      };
    }

    try {
      if (ability) {
        ability.throwUnlessCan('read', this.entityModel.modelName);

        queryCriteria = {
          ...toMongoQuery(ability, this.entityModel.modelName),
          ...queryCriteria,
        };
      }

      query = this.entityModel.find(queryCriteria);

      if (params.populate) {
        query = query.populate(params.populate);
      }

      if (params.sort) {
        query = query.sort(params.sort);
      }

      if (params.select) {
        query = query.select(params.select);
      }

      return query.exec();
    } catch (error) {
      if (error instanceof ForbiddenError) {
        throw new ForbiddenException(error.message);
      }

      throw error;
    }
  }

CodePudding user response：

Try this aggregation pipeline:

db.invoiceParams.aggregate([
   {
      $set: {
         expectedPaid: { $subtract: ["$amount.expected", "$amount.paid"] },
         averageDate: { $toDate: { $avg: [{ $toLong: "$uploadedAt" }, { $toLong: "$paidAt" }] } }
      }
   }
])