Home > Enterprise >  Flatten group of multiple fields in mongo?
Flatten group of multiple fields in mongo?

Time:08-29

I want to get the sum of amounts grouped by address and date:

db.coolCollection.aggregate([ 
{ $group: 
  { _id : 
    { address:"$address", 
      date: { $dateFromString: { dateString: "$block.time"}}}}, 
  sum : { $sum:{ "$amount" }}} ])

Great, except the results look like this:

{
  _id: {
    address: "abc123",
    date: 2021-03-22T00:00:00.000 00:00
  },
  sum: 48645
}

I want this:

{
  address: "abc123",
  date: 2021-03-22T00:00:00.000 00:00,
  sum: 48645
}

CodePudding user response:

Usually you'd just add a $project stage to restructure, here is how to do is using $replaceRoot under the assumption the _id can contain many fields you don't want to manually convert:

db.collection.aggregate([
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$_id",
          "$$ROOT"
        ]
      }
    }
  },
  {
    $project: {
      _id: 0
    }
  }
])

Mongo Playground

  • Related