Subquery with total

Working with Data
2

Hello, @Erysson_Barros ! Welcome to the MongoDB community! :wave:

Since you veiled your collection structure and did not provide sample documents from your collections, I will make up my own simple test dataset:

db.employees.insertMany([
  {
    _id: 'E1',
    name: 'Urko',
  },
  {
    _id: 'E2',
    name: 'Sashko',
  },
  {
    _id: 'E3',
    name: 'Ilko',
  },
]); 

db.workdays.insertMany([
  {
    date: ISODate('2023-08-21'),
    employeeId: 'E1',
    usdEarned: 380,
  },
  {
    date: ISODate('2023-08-22'),
    employeeId: 'E1',
    usdEarned: 430,
  },
  {
    date: ISODate('2023-08-21'),
    employeeId: 'E2',
    usdEarned: 450,
  },
  {
    date: ISODate('2023-08-22'),
    employeeId: 'E2',
    usdEarned: 0,
  },
]);

db.overtime.insertMany([
  {
    date: ISODate('2023-08-20'),
    employeeId: 'E1',
    usdEarned: 120,
  },
]);

Solution 1 (with pipeline in $lookup)

// Solution 1 (with pipeline in $lookup)
db.employees.aggregate([
  {
    $lookup: {
      from: 'workdays',
      let: {
        employeeId: '$_id',
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: ['$employeeId', '$$employeeId']
            }
          }
        },
        {
          $group: {
            _id: null,
            total: {
              $sum: '$usdEarned'
            }
          }
        }
      ],
      as: 'earnings'
    }
  },
  {
    $lookup: {
      from: 'overtime',
      let: {
        employeeId: '$_id',
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: ['$employeeId', '$$employeeId']
            }
          }
        },
        {
          $group: {
            _id: null,
            total: {
              $sum: '$usdEarned'
            }
          }
        }
      ],
      as: 'overtime'
    }
  },
  {
    $unwind: {
      path: '$earnings',
      preserveNullAndEmptyArrays: true,
    }
  },
  {
    $unwind: {
      path: '$overtime',
      preserveNullAndEmptyArrays: true,
    }
  },
  {
    $project: {
      name: '$name',
      totalUsdEarned: {
        $sum: ['$earnings.total', '$overtime.total']
      }
    }
  }
]);

Solution 2: With sequential $lookup + $group + $unwind stages

// Solution 2: With sequential $lookup + $group + $unwind stages
db.employees.aggregate([
  {
    $lookup: {
      from: 'workdays',
      localField: '_id',
      foreignField: 'employeeId',
      as: 'earnings'
    }
  },
  {
    $unwind: {
      path: '$earnings',
      preserveNullAndEmptyArrays: true,
    }
  },
  {
    $group: {
      _id: {
        _id: '$_id',
        name: '$name',
      },
      totalUsdEarned: {
        $sum: '$earnings.usdEarned'
      }
    }
  },
  {
    $lookup: {
      from: 'overtime',
      localField: '_id._id',
      foreignField: 'employeeId',
      as: 'overtime'
    }
  },
  {
    $unwind: {
      path: '$overtime',
      preserveNullAndEmptyArrays: true,
    }
  },
  {
    $group: {
      _id: {
        name: '$_id.name',
        totalUsdEarned: '$totalUsdEarned',
      },
      totalUsdFromOvertime: {
        $sum: '$overtime.usdEarned'
      }
    }
  },
  {
    $project: {
      _id: false,
      name: '$_id.name',
      totalUsdEarned: {
        $sum: ['$_id.totalUsdEarned','$totalUsdFromOvertime']
      }
    }
  }
]);

Output is the same for both aggregations:

[
  { name: 'Urko', totalEarned: 810 },
  { name: 'Ilko', totalEarned: 0 },
  { name: 'Sashko', totalEarned: 450 }
]

Feel free to adapt before usage any of those approaches whichever you feel is more convenient or efficient in your specific use case :wink:

2 Likes