MongoDB get $sum of fields created via $addFields

Question

I'm trying to get sum of fields that were created with $addFields operator. I'd like to get sum of fields for the first month among all documents.

Please see link to the MongoDB sandbox.

Data:

[
  {
    "key": 1,
    "account": "a",
    "cases_total_date": {
      "20220101": 1,
      "20220102": 2,
      "20220103": 3,
      "20220501": 4,
      "20221201": 5,
      "20221202": 6,
      
    }
  },
  {
    "key": 2,
    "account": "b",
    "cases_total_date": {
      "20220101": 11,
      "20220102": 12,
      "20220103": 13,
      "20220501": 14,
      "20221201": 15,
      "20221202": 16,
      
    }
  }
]

Query I've tried:

db.collection.aggregate([
  {
    "$match": {
      "account": {
        "$in": [
          "a",
          "b"
        ]
      }
    }
  },
  {
    "$addFields": {
      "cases_total_months|202201": {
        "$sum": [
          "$cases_total_date.20220101",
          "$cases_total_date.20220102",
          "$cases_total_date.20220103"
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "",
      "cases_total_months|202201_all": {
        "$sum": "$cases_total_months|20220101"
      }
    }
  }
])

The response I've got vs expected:

[
  {
    "_id": "",
    "cases_total_months|202201_all": 0  # EXPECTED sum of fields from 2 docs 6+36=42
  }
]

Would appreciate any feedback. Thank you!

Answer 1

Using dynamic values as field names is considered an anti-pattern and introduces unnecessary complexity to the queries. With a proper schema, you can do something simple as this:

db.collection.aggregate([
  {
    "$set": {
      "cases_total_months|202201_all": {
        "$filter": {
          "input": "$cases_total_date",
          "as": "ctd",
          "cond": {
            $and: [
              {
                $eq: [
                  2022,
                  {
                    $year: "$$ctd.date"
                  }
                ]
              },
              {
                $eq: [
                  1,
                  {
                    $month: "$$ctd.date"
                  }
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      "cases_total_months|202201_all": {
        $sum: {
          $sum: "$cases_total_months|202201_all.value"
        }
      }
    }
  }
])

Mongo Playground

---

For your current schema, you can still rely on $objectToArray and iterate through the resulting k-v tuples to get what you need.

db.collection.aggregate([
  {
    $set: {
      cases_total_date: {
        "$objectToArray": "$cases_total_date"
      }
    }
  },
  {
    $set: {
      "cases_total_months|202201_all": {
        "$filter": {
          "input": "$cases_total_date",
          "as": "ctd",
          "cond": {
            $eq: [
              0,
              {
                "$indexOfCP": [
                  "$$ctd.k",
                  "202201"
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    $set: {
      "cases_total_months|202201_all": {
        $sum: "$cases_total_months|202201_all.v"
      }
    }
  },
  {
    $group: {
      _id: null,
      "cases_total_months|202201_all": {
        $sum: "$cases_total_months|202201_all"
      }
    }
  }
])

Mongo Playground