Conditional sum aggregation pipeline

Working with Data
#1

Is it possibile to create a pipeline for something like this? I wanted the value in the aggregated collection to be calculated according to the operation in the second collection.

/* First collection */
{
  "product": "test",
  "labels" : [
    {"code": "label1", "value": 42},
    {"code": "label2", "value": 50}
  ]
}

/* Second collection */
{
  "code": "label3",
  "calculation" : [
    {"label" : "label1", "operation":"+"},
    {"label" : "label2", "operation":"-"}
  ]
}

In my aggregated collection i want a new field that would be label1 - label2.

{
  "product" : "test", 
  "labels" : [
    {"code": "label1", "value": 42},
    {"code": "label2", "value": 50}
  ], 
  "vlabels" : [
    {"code": "label3", "value": -8}
  ]
}
#2

Hello Ciprian_Stanciu :wave:

How are the two collections, First and Second, linked - by the field product?

#3

hello @Prasad_Saya ,
They are not linked actually. The second collection only holds the calculation method for some virtual labels with values from the first collection.
To be clear every product in the first collection has some physical labels with values. And i need to add those virtual ones calculated according to the operation (either ‘+’ or ‘-’).

#4

Here is the aggregation:

db.first.aggregate( [
  { 
      $unwind: "$labels" 
  },
  { 
      $lookup: {
           from: "second",
           localField: "labels.code",
           foreignField: "calculation.label",
           as: "matches"
      }
  },
  { 
      $unwind: "$matches" 
  },
  { 
      $addFields: { 
          op: { 
             $arrayElemAt: [ 
                 {
                   $filter: {
                       input: "$matches.calculation", 
                       as: "calc",
                       cond: { $eq: [ "$$calc.label", "$labels.code" ] }
                 } }, 
                 0 
             ] 
          } 
      } 
  },
  { 
      $addFields: {
          op_value: {
              $switch: {
                 branches: [
                   { case: { $eq: [ "$op.operation", "+" ] }, then: "$labels.value" },
                   { case: { $eq: [ "$op.operation", "-" ] }, then: { $multiply: [ "$labels.value", -1 ] } }
                 ]
              }
          }
      } 
  },
  { 
      $group: { 
          _id: { _id: "$_id", product: "$product" }, 
          labels: { $push: "$labels" },
          code: { $first: "$matches.code" }, 
          value: { $sum: "$op_value" } 
      } 
  },
  { 
      $project: { 
         _id: "$_id._id", 
         product: "$_id.product", 
         labels: 1, 
         "vlabels.code": "$code",  
         "vlabels.value": "$value"  
      } 
  }
] ).pretty()

The output:

{
        "_id" : ObjectId("5ebd33aba0b845b22f4c8ace"),
        "product" : "test",
        "labels" : [
                {
                        "code" : "label1",
                        "value" : 42
                },
                {
                        "code" : "label2",
                        "value" : 50
                }
        ],
        "vlabels" : {
                "code" : "label3",
                "value" : -8
        }
}