CODEWITHSUNDEEP
javascriptmongodbmongooseaggregation-framework
Ciprian Stanciu
Ciprian Stanciu

Reputation: 5

MongoDB aggregation sum according to document value

I want to create a mongo db view from two collections with a new value that is a sum of values from one of the collection according to an operation from another collection. Below is the structure:

/* 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}
  ]
}

Upvotes: 0

Views: 59

Answers (1)

thammada.ts
thammada.ts

Reputation: 5245

Although it is possible. I doubt it would be optimal, if you don't need further processing on the database, I suggest you do this at the application layer.

However, I have attempted to do this as an exercise. This approach would check only for the "-" operator and assign a negative value, other operator will use the existing value.

/* First collection: "products" */
/* Second collection: "vlabels" */
db.products.aggregate([
  {
    $lookup: {
      from: "vlabels", // lookup calculation from vlabels
      let: {
        labels: "$labels"
      },
      pipeline: [
        {
          $set: {
            calculation: {
              $map: {
                input: "$calculation", // map over calculation in vlabels
                as: "calc",
                in: {
                  operation: "$$calc.operation",
                  product: {
                    $arrayElemAt: [
                      {
                        $filter: {
                          input: "$$labels", // filter for matching product labels and get the first element using $arrayAlemAt to get the value
                          as: "label",
                          cond: {
                            $eq: ["$$calc.label", "$$label.code"]
                          }
                        }
                      },
                      0
                    ]
                  }
                }
              }
            }
          }
        },
        {
          $project: {
            _id: false,
            code: "$code",
            value: {
              $reduce: { // reducing by adding all values in calculation array, use negative value on "-" operator
                input: "$calculation",
                initialValue: 0,
                in: {
                  $add: [
                    "$$value",
                    {
                      $cond: [
                        {
                          $eq: ["-", "$$this.operation"]
                        },
                        {
                          $multiply: [
                            -1,
                            { $ifNull: ["$$this.product.value", 0] }
                          ]
                        },
                        { $ifNull: ["$$this.product.value", 0] }
                      ]
                    }
                  ]
                }
              }
            }
          }
        }
      ],
      as: "vlabels"
    }
  }
])

Mongo Playground

Upvotes: 1

Related Questions