Subquery with total

[Erysson_Barros]

Hi,

I would like your help!

table_1
_id
id_table_3

table_2
_id
id_table_1
– subquery - sum total tb_3_valor

table_3
_id
tb_3_valor

const collection =  db.collection('table_2').aggregate([
         {
         $lookup: {
            from: "table_1",
            let: {
               id_table_3: "$_id"
            },
            pipeline: [
            {
               $match: {
                  $expr: {
                  $eq: [
                     "$$id_table_3",
                     "$id_table_3"
                  ]
                  }
               }
            },
            {
               $lookup: {
                  from: "table_3",
                  let: {
                     table_1_id: "$_id"
                  },
                  pipeline: [
                  {
                     $match: {
                        $expr: {
                        $eq: [
                           "$$table_1_id",
                           "$table_1_id"
                        ]
                        }
                     }
                  },      
                  as: "dot_resultado_01"
               }
            }
            ],
            as: "orc_resultado_02"
         },
         
      },
  { "$group": {
   "_id": { campo:"$_id", somar: "$orc_resultado_02.dot_resultado_01.tb_3_valor",}
   }},

]).toArray();

I would like to remove these lines from the array

---

[],
[],
[]

---

result: 
[
	{
		"_id": {
			"campo": "5545454545454545",
			"somar": [
				[
					{
						"$numberDecimal": "555"
					}
				]
			]
		}
	},
	{
		"_id": {
			"campo": "54555454545454",
			"somar": []
		}
	},
	{
		"_id": {
			"campo": "887854545454",
			"somar": [
				[],
				[
					{
						"$numberDecimal": "223.00"
					}
				],
				[],
				[],
				[]
			]
		}
	}
]

[slava]

Hello, @Erysson_Barros ! Welcome to the MongoDB community!

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

[Erysson_Barros]

hi @slava
thank you very much! it was a big help!