db.getCollection('requests').aggregate([
{
$lookup: {
from: 'bookings',
localField: '_id',
foreignField: 'requests',
as: 'bookings',
},
},
{
$lookup: {
from: 'campaigns',
localField: 'meta.campaign',
foreignField: 'id',
as: 'campaign',
},
},
{ $unwind: { path: '$campaign', preserveNullAndEmptyArrays: true } },
{ $unwind: { path: '$bookings', preserveNullAndEmptyArrays: true } },
{
$lookup: {
from: 'flags',
localField: 'bookings.flags',
foreignField: '_id',
as: 'bookings.flags',
},
},
{
$group: {
_id: '$_id',
bookings: {
$addToSet: {
$cond: {
if: { $not: '$bookings._id' },
then: '$$REMOVE',
else: '$bookings',
},
},
},
campaign: { $first: '$campaign' },
dateRequest: { $first: '$dateRequest' },
meta: { $first: '$meta' },
},
},
{
$group: {
_id: '$meta.campaign',
name: { $first: '$campaign.name' },
requestcount: { $sum: 1 },
earliestrequest: { $min: '$dateRequest' },
revenuetotal: { $sum: { $sum: '$bookings.revenue' } },
bookingcount: { $sum: { $size: '$bookings' } },
bookingFlags: { $push: '$bookings.flags' },
},
},
{
$project: {
_id: 1,
name: 1,
requestcount: 1,
earliestrequest: 1,
revenuetotal: 1,
bookingcount: 1,
quota: {
$cond: {
if: { $gte: ['$requestcount', 1] },
then: {
$divide: [{ $multiply: ['$bookingcount', 100] }, '$requestcount'],
},
else: 0,
},
},
revenueperbooking: {
$cond: {
if: { $gte: ['$bookingcount', 1] },
then: { $divide: ['$revenuetotal', '$bookingcount'] },
else: 0,
},
},
revenueperrequest: {
$cond: {
if: { $gte: ['$requestcount', 1] },
then: { $divide: ['$revenuetotal', '$requestcount'] },
else: 0,
},
},
bookingFlags: {
$reduce: {
input: '$bookingFlags',
initialValue: [],
in: { $concatArrays: ['$$value', '$$this'] },
},
},
},
},
]);