CODEWITHSUNDEEP
mongodbaggregation-frameworkaggregation
francis
francis

Reputation: 4515

How to get the sum field value grouped by date using MongoDB aggregations?

Am working on a toll collection application and want to generate a report based on how much was collected on watch toll plaza on a daily basis.

The way am currently doing it is I have a collection with the dateString field that holds the unique date and names of all plazas as fields and I use this code:

const takeLast = str => str.split(/\W/gi).pop().toLowerCase()

const receipt async () => {
    let doc = await Receipt.findOne({dateString: dates.localeString(receipt.date)})

    if (!doc) doc = await new Receipt({
        dateString: dates.localeString(receipt.date)
    })

    doc.total = doc.total + +receipt.amount
    doc[takeLast(receipt.plaza)] = doc[takeLast(receipt.plaza)] + receipt.amount
    await doc.save()

    return doc
}

So given data like this in the receipts collection:

[
    {
        "_id" : ObjectId("5eef9ddb09fb776392b7e00c"),
        "date" : ISODate("2020-06-09T15:56:05.777Z"),
        "amount" : 20,
        "plaza" : "Chongwe"
    },
    {
        "_id" : ObjectId("5eef9ddb09fb776392b7e00d"),
        "date" : ISODate("2020-06-16T13:33:41.387Z"),
        "amount" : 20,
        "plaza" : "Shimabala"
    },
    {
        "_id" : ObjectId("5eef9ddb09fb776392b7e00e"),
        "date" : ISODate("2020-06-15T14:52:51.420Z"),
        "amount" : 20,
        "plaza" : "Chongwe"
    },
    {
        "_id" : ObjectId("5eef9ddb09fb776392b7e00f"),
        "date" : ISODate("2020-06-15T14:52:13.603Z"),
        "amount" : 20,
        "plaza" : "Chongwe"
    },
    {
        "_id" : ObjectId("5eef9ddb09fb776392b7e010"),
        "date" : ISODate("2020-06-16T16:22:53.823Z"),
        "amount" : 20,
        "plaza" : "Mumbwa"
    },
    {
        "_id" : ObjectId("5eef9ddb09fb776392b7e011"),
        "date" : ISODate("2020-06-20T16:32:50.950Z"),
        "amount" : 20,
        "plaza" : "Chongwe"
    },
    {
        "_id" : ObjectId("5eef9ddb09fb776392b7e012"),
        "date" : ISODate("2020-06-16T13:34:18.070Z"),
        "amount" : 20,
        "plaza" : "Shimabala"
    },
    {
        "_id" : ObjectId("5eef9ddb09fb776392b7e013"),
        "date" : ISODate("2020-06-16T16:24:09.577Z"),
        "amount" : 20,
        "plaza" : "Mumbwa"
    },
    {
        "_id" : ObjectId("5eef9ddb09fb776392b7e014"),
        "date" : ISODate("2020-06-20T16:33:41.687Z"),
        "amount" : 20,
        "plaza" : "Chongwe"
    },
    {
        "_id" : ObjectId("5eef9ddb09fb776392b7e015"),
        "date" : ISODate("2020-06-09T15:55:26.727Z"),
        "amount" : 20,
        "plaza" : "Chongwe"
    },
    {
        "_id" : ObjectId("5eef9ddb09fb776392b7e016"),
        "date" : ISODate("2020-05-31T11:13:14.800Z"),
        "amount" : 20,
        "plaza" : "Chongwe"
    },
    {
        "_id" : ObjectId("5eef9ddb09fb776392b7e017"),
        "date" : ISODate("2020-05-31T11:10:59.927Z"),
        "amount" : 20,
        "plaza" : "Chongwe"
    },
    {
        "_id" : ObjectId("5ef0448b81b0d201ce583251"),
        "date" : ISODate("2020-06-02T09:57:00.090Z"),
        "amount" : 150,
        "plaza" : "Katuba"
    },
    {
        "_id" : ObjectId("5ef4ae62c746cb18df06ab24"),
        "date" : ISODate("2020-06-25T14:01:16.547Z"),
        "amount" : 20,
        "plaza" : "Katuba"
    },
    {
        "_id" : ObjectId("5ef70897a91690446433c601"),
        "date" : ISODate("2020-06-27T08:49:05.053Z"),
        "amount" : 20,
        "plaza" : "Chongwe"
    },
    {
        "_id" : ObjectId("5ef761ace88e61128cda7fc8"),
        "date" : ISODate("2020-06-27T15:11:07.123Z"),
        "amount" : 20,
        "plaza" : "Chongwe"
    },
    {
        "_id" : ObjectId("5ef8470cb84410217d72da18"),
        "date" : ISODate("2020-06-28T07:22:03.647Z"),
        "amount" : 20,
        "plaza" : "Chongwe"
    }
]

I expect the following results when I run a db.receiptReport.find() query:

[
    {
        "_id" : ObjectId("5f45c7501fe3d5059d89b0d2"),
        "dateString" : "2020-06-09",
        "total" : 40,
        "manyumbi" : 0,
        "katuba" : 0,
        "shimabala" : 0,
        "kafulafuta" : 0,
        "sata" : 0,
        "mulemba" : 0,
        "munkombwe" : 0,
        "kavindele" : 0,
        "livingstone" : 0,
        "kazungula" : 0,
        "mumbwa" : 0,
        "mpika" : 0,
        "kapiri" : 0,
        "chongwe" : 40,
        "chembe" : 0,
        "kalense" : 0,
        "katenshi" : 0,
        "kakonde" : 0,
        "chilonga" : 0,
        "chakulya" : 0,
        "mpongwe" : 0,
        "ntoposhi" : 0
    },
    {
        "_id" : ObjectId("5f45c7501fe3d5059d89b0d3"),
        "dateString" : "2020-06-16",
        "total" : 80,
        "manyumbi" : 0,
        "katuba" : 0,
        "shimabala" : 40,
        "kafulafuta" : 0,
        "sata" : 0,
        "mulemba" : 0,
        "munkombwe" : 0,
        "kavindele" : 0,
        "livingstone" : 0,
        "kazungula" : 0,
        "mumbwa" : 40,
        "mpika" : 0,
        "kapiri" : 0,
        "chongwe" : 0,
        "chembe" : 0,
        "kalense" : 0,
        "katenshi" : 0,
        "kakonde" : 0,
        "chilonga" : 0,
        "chakulya" : 0,
        "mpongwe" : 0,
        "ntoposhi" : 0
    },
    {
        "_id" : ObjectId("5f45c7501fe3d5059d89b0d4"),
        "dateString" : "2020-06-15",
        "total" : 40,
        "manyumbi" : 0,
        "katuba" : 0,
        "shimabala" : 0,
        "kafulafuta" : 0,
        "sata" : 0,
        "mulemba" : 0,
        "munkombwe" : 0,
        "kavindele" : 0,
        "livingstone" : 0,
        "kazungula" : 0,
        "mumbwa" : 0,
        "mpika" : 0,
        "kapiri" : 0,
        "chongwe" : 40,
        "chembe" : 0,
        "kalense" : 0,
        "katenshi" : 0,
        "kakonde" : 0,
        "chilonga" : 0,
        "chakulya" : 0,
        "mpongwe" : 0,
        "ntoposhi" : 0
    },
    {
        "_id" : ObjectId("5f45c7501fe3d5059d89b0d5"),
        "dateString" : "2020-06-20",
        "total" : 40,
        "manyumbi" : 0,
        "katuba" : 0,
        "shimabala" : 0,
        "kafulafuta" : 0,
        "sata" : 0,
        "mulemba" : 0,
        "munkombwe" : 0,
        "kavindele" : 0,
        "livingstone" : 0,
        "kazungula" : 0,
        "mumbwa" : 0,
        "mpika" : 0,
        "kapiri" : 0,
        "chongwe" : 40,
        "chembe" : 0,
        "kalense" : 0,
        "katenshi" : 0,
        "kakonde" : 0,
        "chilonga" : 0,
        "chakulya" : 0,
        "mpongwe" : 0,
        "ntoposhi" : 0
    },
    {
        "_id" : ObjectId("5f45c7501fe3d5059d89b0d6"),
        "dateString" : "2020-05-31",
        "total" : 40,
        "manyumbi" : 0,
        "katuba" : 0,
        "shimabala" : 0,
        "kafulafuta" : 0,
        "sata" : 0,
        "mulemba" : 0,
        "munkombwe" : 0,
        "kavindele" : 0,
        "livingstone" : 0,
        "kazungula" : 0,
        "mumbwa" : 0,
        "mpika" : 0,
        "kapiri" : 0,
        "chongwe" : 40,
        "chembe" : 0,
        "kalense" : 0,
        "katenshi" : 0,
        "kakonde" : 0,
        "chilonga" : 0,
        "chakulya" : 0,
        "mpongwe" : 0,
        "ntoposhi" : 0
    },
    {
        "_id" : ObjectId("5f45c7501fe3d5059d89b0d7"),
        "dateString" : "2020-06-02",
        "total" : 150,
        "manyumbi" : 0,
        "katuba" : 150,
        "shimabala" : 0,
        "kafulafuta" : 0,
        "sata" : 0,
        "mulemba" : 0,
        "munkombwe" : 0,
        "kavindele" : 0,
        "livingstone" : 0,
        "kazungula" : 0,
        "mumbwa" : 0,
        "mpika" : 0,
        "kapiri" : 0,
        "chongwe" : 0,
        "chembe" : 0,
        "kalense" : 0,
        "katenshi" : 0,
        "kakonde" : 0,
        "chilonga" : 0,
        "chakulya" : 0,
        "mpongwe" : 0,
        "ntoposhi" : 0
    },
    {
        "_id" : ObjectId("5f45c7501fe3d5059d89b0d8"),
        "dateString" : "2020-06-25",
        "total" : 20,
        "manyumbi" : 0,
        "katuba" : 20,
        "shimabala" : 0,
        "kafulafuta" : 0,
        "sata" : 0,
        "mulemba" : 0,
        "munkombwe" : 0,
        "kavindele" : 0,
        "livingstone" : 0,
        "kazungula" : 0,
        "mumbwa" : 0,
        "mpika" : 0,
        "kapiri" : 0,
        "chongwe" : 0,
        "chembe" : 0,
        "kalense" : 0,
        "katenshi" : 0,
        "kakonde" : 0,
        "chilonga" : 0,
        "chakulya" : 0,
        "mpongwe" : 0,
        "ntoposhi" : 0
    },
    {
        "_id" : ObjectId("5f45c7501fe3d5059d89b0d9"),
        "dateString" : "2020-06-27",
        "total" : 40,
        "manyumbi" : 0,
        "katuba" : 0,
        "shimabala" : 0,
        "kafulafuta" : 0,
        "sata" : 0,
        "mulemba" : 0,
        "munkombwe" : 0,
        "kavindele" : 0,
        "livingstone" : 0,
        "kazungula" : 0,
        "mumbwa" : 0,
        "mpika" : 0,
        "kapiri" : 0,
        "chongwe" : 40,
        "chembe" : 0,
        "kalense" : 0,
        "katenshi" : 0,
        "kakonde" : 0,
        "chilonga" : 0,
        "chakulya" : 0,
        "mpongwe" : 0,
        "ntoposhi" : 0
    },
    {
        "_id" : ObjectId("5f45c7501fe3d5059d89b0da"),
        "dateString" : "2020-06-28",
        "total" : 20,
        "manyumbi" : 0,
        "katuba" : 0,
        "shimabala" : 0,
        "kafulafuta" : 0,
        "sata" : 0,
        "mulemba" : 0,
        "munkombwe" : 0,
        "kavindele" : 0,
        "livingstone" : 0,
        "kazungula" : 0,
        "mumbwa" : 0,
        "mpika" : 0,
        "kapiri" : 0,
        "chongwe" : 20,
        "chembe" : 0,
        "kalense" : 0,
        "katenshi" : 0,
        "kakonde" : 0,
        "chilonga" : 0,
        "chakulya" : 0,
        "mpongwe" : 0,
        "ntoposhi" : 0
    },
    {
        "_id" : ObjectId("5f45c7501fe3d5059d89b0db"),
        "dateString" : "2020-06-29",
        "total" : 40,
        "manyumbi" : 0,
        "katuba" : 40,
        "shimabala" : 0,
        "kafulafuta" : 0,
        "sata" : 0,
        "mulemba" : 0,
        "munkombwe" : 0,
        "kavindele" : 0,
        "livingstone" : 0,
        "kazungula" : 0,
        "mumbwa" : 0,
        "mpika" : 0,
        "kapiri" : 0,
        "chongwe" : 0,
        "chembe" : 0,
        "kalense" : 0,
        "katenshi" : 0,
        "kakonde" : 0,
        "chilonga" : 0,
        "chakulya" : 0,
        "mpongwe" : 0,
        "ntoposhi" : 0
    }
]

How can the same be achieved using aggregations?

Upvotes: 2

Views: 67

Answers (1)

varman
varman

Reputation: 8894

The code is

db.collection.aggregate([
  {
    $addFields: {
      dateOnly: {
        "date": { "$dateToString": { "format": "%Y-%m-%d",  "date": "$date" } }
      }
    }
  },
  {
    $group: {
      _id: null,
      data: { $push: "$$ROOT" },
      allPlaza: { $addToSet: "$plaza" }
    }
  },
  {
    $unwind: "$data"
  },
  {
    "$replaceRoot": {
      "newRoot": { "$mergeObjects": [ "$data", "$$ROOT" ] }
    }
  },
  {
    $group: {
      _id: { date: "$dateOnly.date", plaza: "$plaza" },
      amount: { $sum: "$amount" },
      allPlaza: { "$first": "$allPlaza" }
    }
  },
  {
    $group: {
      _id: "$_id.date",
      plaza: {
        $push: {
          plaza: "$_id.plaza",
          amount: { $sum: "$amount" }
        }
      },
      allPlaza: { "$first": "$allPlaza" }
    }
  },
  {
    $project: {
      data: {
        $map: {
          input: "$plaza",
          in: {
            k: "$$this.plaza",
            v: "$$this.amount"
          }
        }
      },
      allPlaza: {
        $map: {
          input: "$allPlaza",
          in: {
            k: "$$this",
            v: 0
          }
        }
      }
    }
  },
  {
    $project: {
      data: {
        "$arrayToObject": {
          "$concatArrays": ["$allPlaza",  "$data" ]
        }
      }
    }
  },
  {
    $addFields: {
      sum: {
        $reduce: {
          input: "$data",
          initialValue: 0,
          in: { $add: ["$$this.v", "$$value" ] }
        }
      }
    }
  },
  {
    $project: {
      data: {
        "$arrayToObject": "$data"
      },
      sum: 1
    }
  },
  {
    $addFields: {
      "data.total": "$sum"
    }
  }
])

Working Mongo playground

Upvotes: 1

Related Questions