mongo db how to performe two groups

Question

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

Answer 1

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