MongoDB group by day and name with sum of amount

Question

My document structure sample:

    {
    name: 'John',
    amount: '450.00'
    created_date: '1542283200000'
    },
    {
    name: 'Mark',
    amount: '890.00'
    created_date: '1542851379000'
    },
    {
    name: 'Chris',
    amount: '450.00'
    created_date: '1542851379000'
    },
    {
    name: 'Jack',
    amount: '230.00'
    created_date: '1542851379000'
    }...

I want sum of amount, grouped by date and name. Suppose I pass filter of a from_date and to_date(apart 3 days), I should get:

    {
    name: 'John', amount: '990', date: '20/11'
    },
    {
    name: 'John', amount: '200', date: '21/11'
    },
    {
    name: 'John', amount: '120', date: '22/11'
    },
    {
    name: 'Mark', amount: '323', date: '20/11'
    },
    {
    name: 'Mark', amount: '523', date: '21/11'
    },
    {
    name: 'Mark', amount: '734', date: '22/11'
    },
    {
    name: 'Chris', amount: '353', date: '20/11'
    },
    {
    name: 'Chris', amount: '744', date: '21/11'
    },
    {
    name: 'Chris', amount: '894', date: '22/11'
    },
    {
    name: 'Jack', amount: '534', date: '20/11'
    },
    {
    name: 'Jack', amount: '663', date: '21/11'
    },
    {
    name: 'Jack', amount: '235', date: '22/11'
    }

I know basics of MongoDb and am able to fetch the records aggregate with group by name. I also go through this link, but I am unable to use this as in here they are using ISO time(mine has timestamp) and grouping by only one property.

score 1 · Accepted Answer · answered Dec 01 '18 at 09:35

The main difficulty here is that you store both date (timestamp) and amount as strings. So it might be easy to solve that in MongoDB 4.0 or newer since there are $toLong, $toDate and $toDouble operators available. So having timestamp as ISODate you can use $dateToParts which will give you separate fields for date, month etc. Then you can group by (day, month, name) and use $sum to calculate total amount. Try:

db.col.aggregate([
    {
        $addFields: {
            created_date: { $dateToParts: { date: { $toDate: { $toLong: "$created_date" } } } }
        }
    },
    {
        $group: {
            _id: { 
                name: "$name",
                day: "$created_date.day",
                month: "$created_date.month"
            },
            amount: { $sum: { $toDouble: "$amount" } }
        }
    },
    {
        $project: {
            _id: 0,
            name: "$_id.name",
            amount: 1,
            date: { $concat: [ { $toString: "$_id.day" }, "/", { $toString: "$_id.month" } ] }
        }
    }
])

Outputs:

{ "amount" : 230, "name" : "Jack", "date" : "22/11" }
{ "amount" : 450, "name" : "Chris", "date" : "22/11" }
{ "amount" : 890, "name" : "Mark", "date" : "22/11" }
{ "amount" : 450, "name" : "John", "date" : "15/11" }

Perfect solution!! I have learned so much from this. I have added $match for from/to date and $sort, and it worked as I wanted. Thank you. — sk786, Dec 03 '18 at 06:37

MongoDB group by day and name with sum of amount

1 Answers1