Myujth

For given table:

id | val_a | val_b | mult | category
------------------------------------
 0 | 1 | 2 | 9 | A
 1 | 0 | 3 | 5 | B
 2 | 0.5 | 4 | 6 | C

following query in MySQL

SELECT * FROM table WHERE category IN ('A','B','C') 
AND IF( val_a > 0, val_a, val_b ) * mult = (SELECT MAX( IF( val_a > 0, val_a, val_b ) * mult ) FROM table WHERE category IN ('A','B','C')) LIMIT 1;

will find multiplications of val_a * mult for every row (if val_a == 0 : val_b * mult)

0) 1*9 = 9
1) 3*5 = 15
2) 0.5*4 = 2

and then find the maximum of all answers, and finally return row with ID:1

1) 15

How do I aggregate the same in Mongoose? Even partial answer will help to move forward and to learn.
Tried to start with http://www.querymongo.com/ but without success.

You can use $match with $in to apply filtering condition and then $multiply with $cond to calculate the value based on your formula. Finally you can add $sort with $limit to get MAX value. Try:

db.col.aggregate([
 
 $match: 
 category: $in: [ "A", "B", "C" ] 
 
 ,
 
 $project: 
 _id: 1,
 value: 
 $multiply: [ $cond: [ $ne: [ "$val_a", 0 ] , "$val_a", "$val_b" ] , "$mult" ]
 
 
 ,
 
 $sort: value: -1 
 ,
 
 $limit: 1
 
])

Outputs:

 "_id" : 1, "value" : 15