How to use multiple conditions in $match and sum in $group

[Ojo_Ilesanmi]

I 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.

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 instead

[Doug_Duncan]

Hi @Ojo_Ilesanmi, and welcome to the MongoDB Community forums!

Can you please post some sample documents? This makes it easier for the community members to help you out. Without this we could make assumptions and provide a solution that doesn’t work for you. It’s also helpful to see the output you’re looking for.

[Ojo_Ilesanmi]

Thanks. Here is is a sample document.

{
  "_id": {
    "$oid": "6334cefd0048787d5535ff16"
  },
  "userID": {
    "$oid": "6307baab9f51747015fdb981"
  },
  "aggregatorID": "0000375",
  "firstName": "damola",
  "lastName": "akinkunmi",
  "ref": "80573e71-38c3-4243-8660-f6dc8f988f6a",
  "transactionID": "CLV000AB-2033HRNU-092822472300",
  "totalAmount": {
    "$numberDecimal": "5100.0"
  },
  "transactionAmount": {
    "$numberDecimal": "5100.0"
  },
  "transactionFee": {
    "$numberDecimal": "25.5"
  },
  "actionableAmount": {
    "$numberDecimal": "5074.5"
  },
  "aggregatorCut": {
    "$numberDecimal": "5.1000000000000005"
  },
  "cleverCut": {
    "$numberDecimal": "9.434999999999999"
  },
  "type": "CASH_OUT",
  "responseCode": "00",
  "responseMessage": "APPROVED",
  "preBalance": {
    "$numberDecimal": "18213.125"
  },
  "postBalance": {
    "$numberDecimal": "23287.625"
  },
  "walletHistoryID": 613261,
  "walletID": 1809,
  "walletActionAt": {
    "$date": {
      "$numberLong": "1664405248000"
    }
  },
  "provider": "xxxxxx",
  "slug": "/cashout/api/v1/transactions/6334cefd0048787d5535ff16",
  "transactionDateTime": {
    "$date": {
      "$numberLong": "1664405248000"
    }
  },
  "createdAt": {
    "$date": {
      "$numberLong": "1664405245000"
    }
  },
  "updatedAt": {
    "$date": {
      "$numberLong": "1664405245000"
    }
  },
  "businessManager": "002",
  "rrn": "092822472300",
  "pan": "539983******3741",
  "terminalID": "CLV000AB",
  "agentID": "0001787",
  "status": "COMPLETED",
  "debited": true,
  "tracked": false
}

I want my output to like this

date : 2022-10-09
CASHOUT : 897663,088,
FUNDS_TRANSFER: 8900877,
WALLET_TOP_UP: 8890000

I look forward to getting help.

[Doug_Duncan]

You have only provided a single document example, but from what I can see it doesn’t line up with the data you expect in your output.

It would be really useful if you provided multiple documents (only include the fields that are necessary) that cover several groupings with output showing the actual values you expect from the sample documents for the output. From the single document provided and the sample output I can’t figure out where FUNDS_TRANSFER and WALLET_TOP_UP come from. You have a value for type that is CASH_OUT, but your output has a field with a similar name, I assume that this field value contains the sum of the post_balance field that you mentioned in the original post, but again us making assumptions leads to frustrations on your part that things don’t work as expected.

[Ojo_Ilesanmi]

{
  "_id": {
    "$oid": "6334d632eb511a7240a338fc"
  },
  "userID": {
    "$oid": "62580d9fe057e46b9184bbd9"
  },
  "aggregatorID": "0000231",

type or paste code here

  "type": "FUNDS_TRANSFER",
  "responseCode": "00",
  "responseMessage": "Successful",
  "preBalance": {
    "$numberDecimal": "112586.39"
  },
  "postBalance": {
    "$numberDecimal": "36566.39"
  },
  
  "transactionDateTime": {
    "$date": {
      "$numberLong": "1664410689000"
    }
  },
  "createdAt": {
    "$date": {
      "$numberLong": "1664407090000"
    }
  },
  "updatedAt": {
    "$date": {
      "$numberLong": "1664407680000"
    }
  },
  "businessManager": "",
  "agentID": "0000665",

}

{
  "_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"
    }
  },

  "createdAt": {
    "$date": {
      "$numberLong": "1664406584000"
    }
  },
  "updatedAt": {
    "$date": {
      "$numberLong": "1664406584000"
    }
  },


}

AIRTIME_VTU is same as WALLET_TOP_UP