Group By Multiple fields

Working with Data
, , ,
1

Hello everyone. I’m currently learning Express and MongoDB using Mongoose in my apps.

I have a case in that I want to get data grouped by date and type.
in another explanation:
I want to get the total amount for each withdraw and add per day.

this is the data that I have at this stage:


{
    "data": [
        {
            "_id": "64a84bbf6182051f094132ae",
            "amount": 850,
            "transactionType": "add",
            "createdAt": "2023-07-06T17:30:39.065Z"
        },
        {
            "_id": "64a84bc76182051f094132b2",
            "amount": 2850,
            "transactionType": "add",
            "createdAt": "2023-07-06T17:30:47.379Z"
        },
        {
            "_id": "64a84bd16182051f094132b6",
            "amount": 740,
            "transactionType": "add",
            "createdAt": "2023-07-07T17:30:57.994Z"
        },
        {
            "_id": "64a84c2c6182051f094132c0",
            "amount": 1400,
            "transactionType": "withdraw",
            "createdAt": "2023-07-07T17:32:28.868Z"
        }
    ]
}

I’m having this result from this query:

const data = await Activity
    .aggregate()
    .match({
        createdAt: {$gt: new Date(new Date(new Date().getTime() - 1000*60*60*24*30))},
        transactionType: {$ne: "convert"},
        createdBy: {$eq: new ObjectId(req.user.userId)}
    })
    .project("transactionType createdAt amount")

I tried to add this group condition but its group just the date:

group(
        {
            _id: { date: "$createdAt", type: "$transactionType"},
            totalAmount: { $sum: "$amount"}
        }
    )

this is what I got from my group function:

{
    "data": [
        {
            "_id": {
                "date": "2023-07-07T17:32:28.868Z",
                "type": "withdraw"
            },
            "totalAmount": 1400
        },
        {
            "_id": {
                "date": "2023-07-06T17:30:47.379Z",
                "type": "add"
            },
            "totalAmount": 2850
        },
        {
            "_id": {
                "date": "2023-07-06T17:30:39.065Z",
                "type": "add"
            },
            "totalAmount": 850
        },
        {
            "_id": {
                "date": "2023-07-07T17:30:57.994Z",
                "type": "add"
            },
            "totalAmount": 740
        }
    ]
}

My wish is to have a result like this or any better way that could provide me the total amount for each withdraw and add per day.

{
      "_id": {
                "date": "2023-07-07T17:30:57.994Z",
                 "xxx": [{
                     "type": "add",
                     "totalAmount": 4450
                 }, {
                    "type": "withdraw",
                     "totalAmount": 1400
                }
            },
            
        },

       "_id": {
                "date": "other date",
                "xxx": "same data as above"
            },
        .......
        },

}

I’ll be grateful for any kind of help and thanks

2

Something like this?

db.getCollection("Test").aggregate([
{
    $group:{
        _id:'$createdAt',
        transactions:{$push:{type:'$transactionType', totalAmount:'$amount'}}
    }
},
{
    $project:{
        _id:0,
        date:'$_id',
        transactions:1
    }
}
])
3

I have had an issue with the createdAt. since it’s a DateTime type I couldn’t group my records with it.

I have changed the group function and date format, and I find an acceptable result:

.group(
        {
            _id: { 
                day: {$dateToString: { format: "%Y-%m-%d", date: "$createdAt"}} ,
                type: "$transactionType"
            },
            totalAmount: { $sum: "$amount"}
        }
    )

image

4

Hello, John. Thanks for your time and your efforts.

I have had an issue with the createdAt. since it’s a DateTime type I couldn’t group my records with it.

please check my solution below. I have changed the date format so I can group my records.

then let me know your comments there if I can do that in a better way.

Thanks, Again!

5

I did wonder about the date time precision in the output!
Did you really want things grouped by day with an array of summed transaction types or what you currently have, as per your last message?

6

Something like this will get you your original desired output:

db.getCollection("Test").aggregate([
{
    $sort:{
        createdAt:1
    }
},
{
    $group:{
        _id:{
            day:{$dateToString: { format: "%Y-%m-%d", date: "$createdAt"}},
            tranType:'$transactionType'
            
        },
        amount:{$sum:'$amount'}
    }
},
{
    $group:{
        _id:'$_id.day',
        transactions:{
            $push:{
                type:'$_id.tranType',
                amount:'$amount'
            }
        }
    }
},
{
    $project:{
        _id:0,
        date:'$_id',
        transactions:1
    }
}
])

Note if you add an index on the createdDate and start with a sort, it’ll hit it if that’s as desired to avoid a colscan. You had a $match in the original post so as long as that’s supported by indexes you should be good.