Mongodb example for aggregate pipeline with facet, lookup, group, project and match queries

Mongodb Aggregate Facet queries

/*
* This is an example of multi stage facet query in MongoDb to cover few possible combinations of query 
* Facets allows us to write multiple indepent queries to get facet result which is usually used for obtaining stats from DB 
* or tags for products or blogs with product count, category count from products list with product count etc.
*/

db.getCollection('applications').aggregate([
  {
    $match: {
      partner: { $in: [ObjectId("5e8ac6806a99770011f2f38b")] },
      createdAt: {
        $gte: ISODate('2020-08-10T08:40:20.172Z'),
        $lt: ISODate('2020-09-15T05:25:35.938Z'),
      }
    },
  },
  // you can use lookup to join another collection, we are fetching payments for application
  // refer: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#examples
  { 
    "$lookup": {
        from: "application",
        localField: "paymentId",
        foreignField: "_id",
        as: "payments"
    }
  },
  { "$addFields": { "dataId": { "$toString": "$bureauDataId" }}},
    {
        $lookup: {
          from: "bureauData",
          localField: "_id",
          foreignField: "dataId",
          as: "data"
        }
      },
    

  {
    // refer: https://docs.mongodb.com/manual/reference/operator/aggregation/facet/#example
    $facet: {

      // calculate total documents matching above condition in `$match` using groupBy query
      // https://docs.mongodb.com/manual/reference/operator/aggregation/bucketAuto/#pipe._S_bucketAuto
      totalByGroupBy: [{ $bucketAuto: { groupBy: '$_id', buckets: 1 } }],
      
      // calculate total documents matching above condition in `$match` using group query
      // refer: https://docs.mongodb.com/manual/reference/operator/aggregation/sum/#examples
      totalByGroup: [{ '$group': { _id: '$_id', count: { '$sum': 1 } } }],


      // total amount for a field stored in string format inside array of objects  
      totalAmount: [
        // match if condition is satisfied
        // refer: https://docs.mongodb.com/manual/reference/operator/aggregation/match/#examples
        { $match: { 'payments.STATUS': 'S' } },
        
        //convert [parentObject1, { payments: [obj1, obj2]}] to [parentObject1, obj1, obj2]
        // refer: https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/#examples
        { $unwind: '$payments' },

        // convert string type to decimal for calculating sum
        // refer: https://docs.mongodb.com/manual/reference/operator/aggregation/toDecimal/index.html
        { $project: { total: { $toDecimal: '$payments.AMOUNT' } } },

        // calculate sum of all the amounts
        // refer: https://docs.mongodb.com/manual/reference/operator/aggregation/sum/#examples
        { $group: { _id: null, totalAmt: { $sum: '$total' } } },
        
        // convert the result back to string from Decimal object BSON
        // refer: https://docs.mongodb.com/manual/reference/operator/aggregation/toString/#example
        { $project: { totalAmt: { $toString: '$totalAmt' } } },
        {
        $project:{
            _id: 0,
            year: {$year: {date: "$createdAt",timezone: "Asia/Kolkata"}},
            createdAt: { $dateToString: { date: "$createdAt", timezone: "Asia/Kolkata"} },


        }
    },
      ],
      
      // count the number of documents with status 'Processing'
      [
        { '$match': { status: 'Processing' } },
        { '$count': 'count' }
      ]
    },
  },
])