Hello there!
There is a daily cronjob which creates a daily report (model name: dailyReport) per customer and its campaigns based on request and booking numbers per country & region. These values are nested in the docs and I need to summarize these values. Up front: I have no influence to the structure and model of dailyReport so I can’t change how the daily cronjob creates those.
Example doc for dailyReport:
{
date: '2021-06-06 22:00:00.000Z',
customerId: '559687785574',
customerName: 'Test A Customer',
campaigns: [ # contains an indefinite number of campaigns
{
_id: '62c5810cbccef7125b81870b',
name: 'newsletter-2021',
requestCount: 4, # sum of countries.requestCount
bookingCount: 2, # sum of countries.bookingCount
revenue: 1500, # sum of countries.revenue
countries: [ # contains an indefinite number of countries
{
countryCode: 'DE',
requestCount: 4, # sum of regions.requestCount
bookingCount: 2, # sum of regions.bookingCount
revenue: 1500, # sum of regions.revenue
regions: [ # contains an indefinite number of regions
{
countryRegion: 'Bayern',
requestCount: 1,
bookingCount: 1,
revenue: 1000
},
{
countryRegion: 'Sachsen',
requestCount: 3,
bookingCount: 1,
revenue: 500
}
]
}
]
}
]
}
So these docs are created for every customer for every day. Now I need to aggregate/sum the data for a single customer within a date range.
How can i aggregate the dailyReports for a single customer grouped by campaigns and sum the nested values for requestCount, bookingCount & revenue for every region, country and campaign?
I tried the following to aggregate the data for may 2021:
[
# limit docs by customer id and requested date range
{ $match: {
customerId: '559687785574',
date: {
$gt: new Date('2021-05-31 22:00:00.000Z'),
$lt: new Date('2021-07-01 22:00:00.000Z'),
}
}},
# group by customer id and push all campaign data in one array
{ $group: {
_id: '$customerId',
customerName: { $first: '$customerName' },
campaigns: { $push: '$campaigns' }
}},
# 'transform' $campaigns to single level array
{ $project: {
_id: 1,
customerName: 1,
campaigns: { $reduce: {
input: '$campaigns',
initialValue: [],
in: { $concatArrays: ['$value', '$this'] },
}}
}}
]
But now I’m stuck because every doc in $campaigns holds its own data for countries and regions, but I need to somehow sum them based on same campaign, country, region data but keep the nesting. So the final array from aggregation should look like this:
[
# ... shorten for readability
{
_id: '62c5810cbccef7125b81870b',
name: 'newsletter-2021',
requestCount: 340,
bookingCount: 157,
revenue: 20358,
countries: [
# ... shorten for readability
{
countryCode: 'DE',
requestCount: 123,
bookingCount: 67,
revenue: 10564,
regions: [
{
countryRegion: 'Bayern',
requestCount: 74,
bookingCount: 37,
revenue: 7854,
},
{
countryRegion: 'Sachsen',
requestCount: 74,
bookingCount: 37,
revenue: 7854,
},
# ... shorten for readability
],
# ... shorten for readability
},
# ... shorten for readability
],
},
# ... shorten for readability
]
I hope I explained myself clear enough and someone can help me.
cheers
Georg
