I have the following document :

{
 _ids : ...
 market : ...
 contractorName : ...
 field :...
 amount : ...
}

and i want to group it first by market then group the result by field (sum of amounts) inside each obtained list as follow :

{
 [
  market : ...
  result : [
            {
             field : ...
             sumOfAmounts : ...
            }
          ]
 ]
}

any idea how to acheive this using springboot mongotemplate or by using raw mongo

CodePudding user response：

Group both market and field first, then group market.

You should group them twice.

And try "Reverse Thinking" of group.

db.collection.aggregate([
  {
    $group: {
      _id: {
        market: "$market",
        field: "$field"
      },
      sumOfAmounts: {
        $sum: "$amount"
      }
    }
  },
  {
    $group: {
      _id: "$_id.market",
      result: {
        $push: {
          field: "$_id.field",
          sumOfAmounts: "$sumOfAmounts"
        }
      }
    }
  },
  {
    $set: {
      market: "$_id"
    }
  }
])

mongoplayground