How to use $match (multiple conditions) and $group in Mongodb

Question

have list of records with the following fields - postBalance, agentId, createdAt, type. I want to filter by “type” and date. After this is done I want to get the $last postBalance for each agent based on the filter and sum up the postBalance. I have been struggling with this using this query

db.transaction.aggregate(
    [{ $match: { 
        $and: [ {
             createdAt: { $gte: ISODate('2022-09-15'), $lt:
             ('2022-09-16') } },
              { type: "CASH_OUT"}]}},
        {
              $group:
         {
            _id: {createdAt: {$last: "$createdAt"}},
           totalAmount: { $sum: "$postBalance" },
           
         }
     }
        
    ]
)

An empty array is returned with this query and there are data in the collection.

Below are samples of the documents

{
  "_id": {
    "$oid": "6334cefd0048787d5535ff16"
  },

  "type": "CASH_OUT",

  "postBalance": {
    "$numberDecimal": "23287.625"
  },
  
  "createdAt": {
    "$date": {
      "$numberLong": "1664405245000"
    }
  },
 
}
{
  "_id": {
    "$oid": "6334d438c1ab8a577677cbf3"
  },
  "userID": {
    "$oid": "62f27bc29f51747015fdb941"
  },
  "aggregatorID": "0000116",
  
  "transactionFee": {
    "$numberDecimal": "0.0"
  },

  "type": "AIRTIME_VTU",
  "postBalance": {
    "$numberDecimal": "2114.675"
  },
  "walletHistoryID": 613266,
  "walletID": 1720,
  "walletActionAt": {
    "$date": {
      "$numberLong": "1664406584000"
    }
  },

{
"type": "FUNDS_TRANSFER",
 
  "postBalance": {
    "$numberDecimal": "36566.39"
  },
  
  
  "createdAt": {
    "$date": {
      "$numberLong": "1664407090000"
    }
  }

}

This is the output I am expecting

{
    "date" : 2022-10-09,
"CASHOUT ": 897663,088,
"FUNDS_TRANSFER": 8900877,
"AIRTIME_VTU": 8890000
}

How can my query be aggregated to get this? Thanks

Answer 1

It look like you want something like:

db.collection.aggregate([
  {$match: {
      createdAt: {
        $gte: ISODate("2022-09-15T00:00:00.000Z"),
        $lt: ISODate("2022-09-30T00:00:00.000Z")
      }
    }
  },
  {$group: {
      _id: "$type",
      createdAt: {$first: "$createdAt"},
      totalAmount: {$sum: "$postBalance"}
    }
  },
  {$group: {
      _id: 0,
      createdAt: {$first: "$createdAt"},
      data: {$push: {k: "$_id", v: "$totalAmount"}}
    }
  },
  {$project: {
      data: {$arrayToObject: "$data"},
      createdAt: 1,
      _id: 0
    }
  },
  {$set: {"data.date": "$createdAt"}},
  {$replaceRoot: {newRoot: "$data"}}
])

See how it works on the playground example