How to fix mongo db query?

Question

I am writing a request for statistics from the db. Here's the code:

Stats.aggregate([
    {
        $group: {
            _id: {
                'advertiser': '$advertiser',
                'offer': '$offer'
            },
            stats: {
                $push: {
                    'date': '$date',
                    'spent': '$spent',
                    'revenue': '$revenue'
                }
            }
        }
    },
    {
        $group: {
            _id: '$_id.advertiser',
            offers: {
                $push: {
                    _id: '$_id.offer',
                    stats: '$stats'
                }
            }
        }
    }], callback);

I want that the advertiser had all his offers, and inside the offers there was statistics on the days along with spent and revenue. The problem is that statistics by day can be more than one and I get this answer:

stats   […]
0   {…}
date    2018-01-30T22:00:00.000Z
spent   100
revenue 200
1   {…}
date    2018-01-30T22:00:00.000Z
spent   20
revenue 20

But, I need to have the same days folded into one and spent and revenue added Also, I want to add offer info from offers collection into result offers, like I do with advertisers. Maybe someone know how to do this. Thank you

Stats in db:

{
"_id": {
    "$oid": "5a4f873d381727000404d171"
},
"advertiser": {
    "$oid": "5a4f74619f0251000438fe4a"
},
"offer": {
    "$oid": "5a4f748c9f0251000438fe4b"
},
"spent": 415.19,
"revenue": 780.92,
"date": "2018-01-02T22:00:00.000Z",
"user": {
    "$oid": "5a4f74259f0251000438fe40"
},
"__v": 0

}

Stats Schema

const StatsSchema = mongoose.Schema({
advertiser: {
    type: ObjectId,
    ref: 'Advertiser',
    required: true
},
offer: {
    type: ObjectId,
    ref: 'Offer',
    required: true
},
spent: {
    type: Number,
    required: true
},
revenue: {
    type: Number,
    required: true
},
date: {
    type: String,
    required: true
},
user: {
    type: ObjectId,
    ref: 'User',
    required: true
}});

Answer 1

You can first project the day, month and year then group accordingly as below. This is a relatively long query you could add in some optimisations based on your workflow.

db.createCollection('statistics');

db.statistics.insertMany([
    {advertiser: "1", offer: "1", date: "2018-01-30T22:00:00.000Z", spent: 10, revenue: 20},
    {advertiser: "1", offer: "1", date: "2018-01-30T21:00:00.000Z", spent: 20, revenue: 20},
    {advertiser: "2", offer: "2", date: "2018-01-30T22:00:00.000Z", spent: 10, revenue: 20},
    {advertiser: "2", offer: "2", date: "2018-01-30T21:00:00.000Z", spent: 1000, revenue: 2000},
    {advertiser: "2", offer: "2", date: "2018-01-31T22:00:00.000Z", spent: 25, revenue: 50}
])

transform_date = {
    $project: {
        advertiser: 1,
        offer: 1,
        date: { $dateFromString: { dateString: { $arrayElemAt: [ {$split: ["$date", "Z"]}, 0 ] }, timezone: 'UTC' } },
        spent: 1,
        revenue: 1      
    }
}

project_year_month_day = {
    $project: {
        advertiser: 1,
        offer: 1,
        date: 1,
        spent: 1,
        revenue: 1,
        year: { $year: "$date" },
        month: { $month: "$date" },
        day: { $dayOfMonth: "$date" }
    }
}

group_by_date_advertiser_offer_and_sum = {
    $group: {
        _id: {
            advertiser: "$advertiser",
            offer: "$offer",
            day: "$day",
            month: "$month",
            year: "$year"
        },
        spent: { $sum: "$spent" },
        revenue: { $sum: "$revenue" },
        dates: { $push: "$date" }
    }
}

group_advertiser_offer_and_push = {
    $group: {
        _id: {
            advertiser: "$_id.advertiser",
            offer: "$_id.offer"
        },
        stats: {
            $push: {
                dates: "$dates",
                spent: "$spent",
                revenue: "$revenue"
            }
        }
    }
}

group_advertiser_and_push = {
    $group: {
        _id: "$_id.advertiser",
        offers: {
            $push: {
                _id: "$_id.offer",
                stats: "$stats"
            }
        }
    }
}

db.statistics.aggregate([
    transform_date, 
    project_year_month_day, 
    group_by_date_advertiser_offer_and_sum, 
    group_advertiser_offer_and_push, 
    group_advertiser_and_push
])

OUTPUT

[{
    "_id": "2",
    "offers": [{
        "_id": "2",
        "stats": [{
            "dates": [ISODate("2018-01-31T22:00:00Z")],
            "spent": 25,
            "revenue": 50
        }, {
            "dates": [ISODate("2018-01-30T22:00:00Z"), ISODate("2018-01-30T21:00:00Z")],
            "spent": 1010,
            "revenue": 2020
        }]
    }]
} {
    "_id": "1",
    "offers": [{
        "_id": "1",
        "stats": [{
            "dates": [ISODate("2018-01-30T22:00:00Z"), ISODate("2018-01-30T21:00:00Z")],
            "spent": 30,
            "revenue": 40
        }]
    }]
}]