sampelquery.mongodb

const query = [
        {
          $lookup: {
            localField: '_id',
            foreignField: 'productionId',
            from: 'production_lead',
            as: 'production_lead'
          }
        },
        {
          $unwind: {
            path: '$production_lead',
            preserveNullAndEmptyArrays: true,
          }
        },

        // join staff
        {
          $lookup: {
            localField: 'production_lead.staffId',
            foreignField: '_id',
            from: 'staff',
            as: 'staff'
          }
        },
        {
          $unwind: {
            path: '$staff',
            preserveNullAndEmptyArrays: true,
          }
        },
        //join freelancer
        {
          $lookup: {
            localField: 'production_lead.freelancerId',
            foreignField: '_id',
            from: 'freelancer',
            as: 'freelancer'
          }
        },
        {
          $unwind: {
            path: '$freelancer',
            preserveNullAndEmptyArrays: true,
          }
        },

        {
          $match: matchQuery,
        },
        {
          $match: {
            $and: [
              {
                'revenue': {$exists: true,}
              },
              {
                'revenue': {$ne: null}
              },
              {
                'revenue': {$gt: 0,}
              }
            ],
          }
        },
        {
          $group: {
            _id: '$_id',
            revenue: {$first: '$revenue'},
            staff: {$first: '$staff'},
            freelancer: {$first: '$freelancer'},
          }
        },
        {

          $project: {
            _id: false,
            revenue: true,
            lead: {
              $cond: {if: {$ne: ['$staff', null]}, then: '$staff', else: '$freelancer'}
            }
          },
        },
        {
          $project: {
            revenue: true,
            lead: {
              _id: '$lead._id',
              firstName: '$lead.firstName',
              lastName: '$lead.lastName',
            }
          }
        },
        {
          $group: {
            _id: '$lead._id',
            lead: {$first: '$lead'},
            revenue: {$sum: '$revenue'},
          }
        },
        {
          $sort: {
            'revenue': -1
          }
        },
        {
          $limit: 10
        }

      ]