MongoDb aggregate values per date

Question

I'm trying to get the aggregate value per cases_total_date keys for such example (please note that I can't change schema of the data, it's already stored like this):

    [
  {
    "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,
      ...
    }
  }
]

In the end I'd like to get result smth like this :

"account": "total",
"cases_total_date": {
  "20220101": 12,   # sum per all dates
  "20220102": 14,   # sum per all dates
  "20220103": 16,   # sum per all dates
  "20220501": 18,   # sum per all dates
  "20221201": 20,   # sum per all dates
  "20221202": 22,   # sum per all dates
  ...
}

But I'm not sure how to make it flexible - cases_total_date keys can differ (different string dates), so I'm looking for a universal approach, where I woudn't need to specify field names. But since I'm not good at mongo, my attempt hasn't led to the result. So if you have some idea, I'd really appreciate it. Thanks!

Answer 1

As mentioned in your previous question, using a proper schema(i.e. without dynamic values as field names and storing dates as proper date objects) would suggest much easier query.

db.collection.aggregate([
  {
    "$match": {
      "account": {
        "$in": [
          "a",
          "b"
        ]
      }
    }
  },
  {
    "$unwind": "$cases_total_date"
  },
  {
    $group: {
      _id: "$cases_total_date.date",
      total: {
        $sum: "$cases_total_date.value"
      }
    }
  },
  {
    $group: {
      _id: null,
      cases_total_date: {
        $push: {
          date: "$_id",
          value: "$total"
        }
      }
    }
  }
])

Mongo Playground

---

For your current schema, the tricks is the same. Use $objectToArray to convert the object into array of k-v tuples. $unwind and re$group the results.

db.collection.aggregate([
  {
    "$match": {
      "account": {
        "$in": [
          "a",
          "b"
        ]
      }
    }
  },
  {
    $set: {
      cases_total_date: {
        "$objectToArray": "$cases_total_date"
      }
    }
  },
  {
    "$unwind": "$cases_total_date"
  },
  {
    $group: {
      _id: "$cases_total_date.k",
      total: {
        $sum: "$cases_total_date.v"
      }
    }
  },
  {
    $group: {
      _id: null,
      cases_total_date: {
        $push: {
          k: "$_id",
          v: "$total"
        }
      }
    }
  },
  {
    "$project": {
      _id: 0,
      account: "total",
      cases_total_date: {
        "$arrayToObject": "$cases_total_date"
      }
    }
  }
])

Mongo Playground