I have many records in my mongodb collection and I need to recount some information.

records format:

{
    "_id" : someId,
    "targetFrom" : ObjectId("603e0355e805140334e79438"),<-- this is ID for search
    "targetTo" : null,
    "operationPaid" : true,
    "type" : "coming", <--- type
    "moneyAccount" : someId,
    "agent" : null,
    "sum" : 5000, <--- sum
}
{
    "_id" : someId,
    "targetFrom" : null,
    "targetTo" : null,
    "operationPaid" : true,
    "type" : "out", <--- type
    "moneyAccount" : someId,
    "agent" : ObjectId("603e0355e805140334e79438"),<-- this is ID for search
    "sum" : 3000, <--- sum
}

so, I need to group by records TYPE and get SUM for id ObjectId("603e0355e805140334e79438"), but id for search can be field targetFrom or targetTo or agent

for this example I need to get result 2000

sum 5000 is coming and sum 3000 is out with

CodePudding user response：

Query

• match the Id in one of the 3 possible fields
• group by null (all collection 1 group), if type="out" i subtract the sum field else i add to sum field

Test code here

aggregate(
[{"$match":
  {"$expr":
   {"$or":
    [{"$eq":["$targetFrom", ObjectId("603e0355e805140334e79438")]},
     {"$eq":["$targetTo", ObjectId("603e0355e805140334e79438")]},
     {"$eq":["$agent", ObjectId("603e0355e805140334e79438")]}]}}},
 {"$group":
  {"_id":null,
   "sum":
   {"$sum":
    {"$cond":
     [{"$eq":["$type", "out"]}, {"$subtract":[0, "$sum"]}, "$sum"]}}}}])