Combine arrays in MongoDB $group aggregation

Question

I am using Mongo daily bucketing pattern. Each daily document contains an array with value calculated for every hour for that day:

{
  meter: 'meterId',
  date: 'dailyBucket',
  hourlyConsumption: [0,0,1,1,1,2,2,2,4,4,4,4,3,3,3...] // array with 24 values for every hour of a day
}

Now in one of my aggregation queries, I would like to group documents for the same day of multiple meters and get a result like this:

INPUT (consumption of multiple meters in a same day)

{
  meter: 'MeterA',
  date: '2021-05-01',
  hourlyConsumption: [0,0,1,1,1,2,2,2,4,4,4,4,3,3,3...]
},
{
  meter: 'MeterB',
  date: '2021-05-01',
  hourlyConsumption: [10,10,10,10,10,10,10,10,10,10,10,10,10,10,10...]
}

RESULT (combined into single document)

{
  date: '2021-05-01',
  hourlyConsumption: [10,10,11,11,11,12,12,12,14,14,14,14,13,13,13...]
}

is there a way to achieve this without using $accumulator?

Wernfried Domscheit · Accepted Answer · 2021-11-26 22:37:38Z

2

You can use $reduce

db.collection.aggregate([
  {
    $group: {
      _id: "$date",
      hourlyConsumption: { $push: "$hourlyConsumption" }
    }
  },
  {
    $set: {
      hourlyConsumption: {
        $reduce: {
          input: "$hourlyConsumption",
          initialValue: [],
          in: { $map: { input: { $range: [ 0, 23 ] },
              as: "h",
              in: {
                $sum: [ 
                  { $arrayElemAt: [ "$$value", "$$h" ] },
                  { $arrayElemAt: [ "$$this", "$$h" ] }
                ]
              }
            }
          }
        }
      }
    }
  }
])

Mongo Playground

Or you use $unwind and $group:

db.collection.aggregate([
  {
    $unwind: {
      path: "$hourlyConsumption",
      includeArrayIndex: "hour"
    }
  },
  {
    $group: {
      _id: {
        date: "$date",
        hour: "$hour"
      },
      hourlyConsumption: { $sum: "$hourlyConsumption" }
    }
  },
  { $sort: { "_id.hour": 1 } },
  {
    $group: {
      _id: "$_id.date",
      hourlyConsumption: { $push: "$hourlyConsumption" }
    }
  }
])

Mongo Playground

However, when you use $unwind, then you actually contradict your bucketing design pattern.

edited Nov 26, 2021 at 22:37

answered Nov 26, 2021 at 22:31

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

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

1 Comment

yunicz Over a year ago

Thank you, I tested the $reduce solution and it is 50 % better performant than $accumulator

Collectives™ on Stack Overflow

Combine arrays in MongoDB $group aggregation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related