Issue with MongoDB array aggregation and grouping by key in array of objects

Question

Considering an array like the one below which i'm trying to group by date and sum the durations:

[
  {
    "allDates": [
      {
        "duration": 153,
        "date": "2021-10"
      },
      {
        "duration": 20,
        "date": "2021-11"
      },
      {
        "duration": 181,
        "date": "2021-11"
      },
      {
        "duration": 180,
        "date": "2021-11"
      }
    ]
  }
]

I'm trying to achieve a sum of the duration but grouped by the dates. This is what i've tried so far:

db.collection.aggregate([
  {
    $addFields: {
      durations: {
        $arrayToObject: {
          $map: {
            input: "$allDates",
            as: "allDate",
            in: {
              k: {
                $toString: "$$allDate.date"
              },
              v: {
                $sum: {
                  $map: {
                    input: "$allDates",
                    as: "kv",
                    in: {
                      $cond: {
                        if: {
                          $eq: [
                            {
                              $toString: "$allDate.date"
                            },
                            {
                              $toString: "$$kv.k"
                            }
                          ]
                        },
                        then: "$$kv.duration",
                        else: 0
                      }
                    }
                  }
                }
              }
            }
          }
        }
      },
      
    }
  }
])

Unfortunately the result i get is:

[
  {
    "allDates": [
      {
        "date": "2021-10",
        "duration": 153
      },
      {
        "date": "2021-11",
        "duration": 20
      },
      {
        "date": "2021-11",
        "duration": 181
      },
      {
        "date": "2021-11",
        "duration": 180
      }
    ],
    "durations": {
      "2021-10": 534,
      "2021-11": 534
    }
  }
]

So it's adding them all up for every key instead of for each one separately, what am i missing here?

Basically I'm expecting to get:

...
"durations": {
      "2021-10": 153, 
      "2021-11": 381 
}

@WernfriedDomscheit "durations": {"2021-10": 153, "2021-11": 381 } basically. So to add the durations per date and not all of them. Edited the question to reflect that. — panicDisco
– panicDisco, Commented Nov 19, 2021 at 19:21

Wernfried Domscheit · Accepted Answer · 2021-11-19 19:36:56Z

1

Try this one:

db.collection.aggregate([
  {
    $unwind: "$allDates"
  },
  {
    $group: {
      _id: "$allDates.date",
      duration: {
        $sum: "$allDates.duration"
      }
    }
  },
  {
    $group: {
      _id: null,
      durations: {
        $push: {
          k: "$_id",
          v: "$duration"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      durations: {
        $arrayToObject: "$durations"
      }
    }
  }
])

Mongo Playground

answered Nov 19, 2021 at 19:36

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Issue with MongoDB array aggregation and grouping by key in array of objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related