Question 116206 in Mongodb Question posted in
The official documentation can be found here.

mongodb – Subtracts two numbers total to return the difference

Consider I have the following collection:

[
  {
    "total": 48.0,
    "status": "CO"
  },
  {
    "total": 11.0,
    "status": "CA"
  },
  {
    "total": 15916.0,
    "status": "PE"
  }
]

I need to realize the difference of PE status – (CO + CA).

The expected result is:

{
  "_id" : null,
  "total" : 15857.0
}
Tags:

2

Answers


  1. 0

    Use $switch to cater for different cases for your sum. Use $subtract to flip the sign for the partial sum.

    db.collection.aggregate([
  {
    $group: {
      _id: null,
      total: {
        "$sum": {
          "$switch": {
            "branches": [
              {
                "case": {
                  $eq: [
                    "$status",
                    "PE"
                  ]
                },
                "then": "$total"
              },
              {
                "case": {
                  $eq: [
                    "$status",
                    "CO"
                  ]
                },
                "then": {
                  $subtract: [
                    0,
                    "$total"
                  ]
                }
              },
              {
                "case": {
                  $eq: [
                    "$status",
                    "CA"
                  ]
                },
                "then": {
                  $subtract: [
                    0,
                    "$total"
                  ]
                }
              }
            ],
            default: 0
          }
        }
      }
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
  2. 0

    Assuming these are the only status options, one way is to $group using $cond:

    db.collection.aggregate([
  {$group: {
      _id: 0,
      total: {
        $sum: {$cond: [{$eq: ["$status", "PE"]}, "$total", {$multiply: ["$total", -1]}]}
      }
  }}
])

    See how it works on the playground example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search