How to group result of different group aggregation

Hello!,

I have problems with writing aggregation with multiple groupings. I have collection:

[
  {
    "_id": {
      "$binary": {
        "base64": "9JzexK9uSO241gskRnnifg==",
        "subType": "04"
      }
    },
    "TransactionDate": {
      "$date": {
        "$numberLong": "1647993600000"
      }
    },
    "Value": {
      "$numberDecimal": "-59.00"
    },
    "CategoryId": {
      "$binary": {
        "base64": "qT1+Iu7QQ2uguFh0I/+JUw==",
        "subType": "04"
      }
    }
  },
  {
    "_id": {
      "$binary": {
        "base64": "88+SyjhBT3+wHyg021UccA==",
        "subType": "04"
      }
    },
    "TransactionDate": {
      "$date": {
        "$numberLong": "1646438400000"
      }
    },
    "Value": {
      "$numberDecimal": "-9.60"
    },
    "CategoryId": {
      "$binary": {
        "base64": "zMHauQ8CSvKYFNOQ39+dwQ==",
        "subType": "04"
      }
    }
  },
  {
    "_id": {
      "$binary": {
        "base64": "wOOxv6KCT0KNpuhWaXs5Mg==",
        "subType": "04"
      }
    },
    "TransactionDate": {
      "$date": {
        "$numberLong": "1646956800000"
      }
    },
    "Value": {
      "$numberDecimal": "-45.00"
    },
    "CategoryId": {
      "$binary": {
        "base64": "qT1+Iu7QQ2uguFh0I/+JUw==",
        "subType": "04"
      }
    }
  },
  {
    "_id": {
      "$binary": {
        "base64": "vRQ6SgAdQCaWdUa1EtNaQg==",
        "subType": "04"
      }
    },
    "TransactionDate": {
      "$date": {
        "$numberLong": "1675123200000"
      }
    },
    "Value": {
      "$numberDecimal": "-18.69"
    },
    "CategoryId": {
      "$binary": {
        "base64": "seHoLjnmSjqeMqzSXk+Dpw==",
        "subType": "04"
      }
    }
  },
  {
    "_id": {
      "$binary": {
        "base64": "UPQwlqV6QvGc2NO8SHexWQ==",
        "subType": "04"
      }
    },
    "TransactionDate": {
      "$date": {
        "$numberLong": "1675123200000"
      }
    },
    "Value": {
      "$numberDecimal": "-15.69"
    },
    "CategoryId": {
      "$binary": {
        "base64": "seHoLjnmSjqeMqzSXk+Dpw==",
        "subType": "04"
      }
    }
  },
  {
    "_id": {
      "$binary": {
        "base64": "iEee/gYlSmKhjYi09CtoAQ==",
        "subType": "04"
      }
    },
    "TransactionDate": {
      "$date": {
        "$numberLong": "1675123200000"
      }
    },
    "Value": {
      "$numberDecimal": "253.81"
    },
    "CategoryId": {
      "$binary": {
        "base64": "WCDMVi7iRPKiPD6Oaqbhhw==",
        "subType": "04"
      }
    }
  },
  {
    "_id": {
      "$binary": {
        "base64": "C+O/awJWSjWhA57QCdvjhA==",
        "subType": "04"
      }
    },
    "TransactionDate": {
      "$date": {
        "$numberLong": "1675123200000"
      }
    },
    "Value": {
      "$numberDecimal": "-15.69"
    },
    "CategoryId": {
      "$binary": {
        "base64": "seHoLjnmSjqeMqzSXk+Dpw==",
        "subType": "04"
      }
    }
  }
]

Using this collection I want result to be grouped by year and month of TransactionDate and then grouped by CategoryId. I have prepared the expected result:

[
  {
    "_id": {
      "month": 3,
      "year": 2022
    }
    "Transactions": [
      {
        "CategoryId": BinData(4, "qT1+Iu7QQ2uguFh0I/+JUw=="),
        "Transactions": [
          {
            "CategoryId": BinData(4, "qT1+Iu7QQ2uguFh0I/+JUw=="),
            "TransactionDate": ISODate("2022-03-11T00:00:00Z"),
            "Value": NumberDecimal("-45.00"),
            "_id": BinData(4, "wOOxv6KCT0KNpuhWaXs5Mg==")
          },
          {
            "CategoryId": BinData(4, "qT1+Iu7QQ2uguFh0I/+JUw=="),
            "TransactionDate": ISODate("2022-03-23T00:00:00Z"),
            "Value": NumberDecimal("-59.00"),
            "_id": BinData(4, "9JzexK9uSO241gskRnnifg==")
          },
        ]
      },
      {
        "CategoryId": BinData(4, "zMHauQ8CSvKYFNOQ39+dwQ=="),
        "Transactions": [
          {
            "CategoryId": BinData(4, "zMHauQ8CSvKYFNOQ39+dwQ=="),
            "TransactionDate": ISODate("2022-03-05T00:00:00Z"),
            "Value": NumberDecimal("-9.60"),
            "_id": BinData(4, "88+SyjhBT3+wHyg021UccA==")
          }
        ]
      },
    ],
  },
  {
    "_id": {
      "month": 1,
      "year": 2023
    }
    "Transactions": [
      {
        "CategoryId": BinData(4, "seHoLjnmSjqeMqzSXk+Dpw=="),
        "Transactions": [
          {
            "CategoryId": BinData(4, "seHoLjnmSjqeMqzSXk+Dpw=="),
            "TransactionDate": ISODate("2023-01-31T00:00:00Z"),
            "Value": NumberDecimal("-18.69"),
            "_id": BinData(4, "vRQ6SgAdQCaWdUa1EtNaQg==")
          },
          {
            "CategoryId": BinData(4, "seHoLjnmSjqeMqzSXk+Dpw=="),
            "TransactionDate": ISODate("2023-01-31T00:00:00Z"),
            "Value": NumberDecimal("-15.69"),
            "_id": BinData(4, "UPQwlqV6QvGc2NO8SHexWQ==")
          },
          {
            "CategoryId": BinData(4, "seHoLjnmSjqeMqzSXk+Dpw=="),
            "TransactionDate": ISODate("2023-01-31T00:00:00Z"),
            "Value": NumberDecimal("-15.69"),
            "_id": BinData(4, "C+O/awJWSjWhA57QCdvjhA==")
          }
        ]
      },
      {
        "CategoryId": BinData(4, "WCDMVi7iRPKiPD6Oaqbhhw=="),
        "Transactions": [
          {
            "CategoryId": BinData(4, "WCDMVi7iRPKiPD6Oaqbhhw=="),
            "TransactionDate": ISODate("2023-01-31T00:00:00Z"),
            "Value": NumberDecimal("253.81"),
            "_id": BinData(4, "iEee/gYlSmKhjYi09CtoAQ==")
          },
        ]
      },
    ],
  }
]

First step is easy for me, because it’s simple group aggregation:

    $group: {
      _id: {
        year: {
          $year: "$TransactionDate",
          
        },
        month: {
          $month: "$TransactionDate",
          
        },
        
      },
      Transactions: {
        $push: "$$ROOT",
      }
    }

But I don’t know, how to group inner Transactions list by CategoryId. I tried to use unwind and then group by CategoryId, but the result is different that I need.

Mongo playground:

You could try to first $group on year/month/category first with

"_id" : {
  "year" : { "$year" : "$TransactionDate" } ,
  "month" : { "$month" : "$TransactionDate" } ,
  "category" : "$CategoryId"
} ,
"Transactions" : { "$push" : "$$ROOT" }

A second $group will then be used to make your year/month group with something like:

"_id" : { "year" : "$_id.year" , "month" : "$_id.month" }
"transactions" : { "$push" : {
    "category" : "$_id.category" ,
    "transactions" : "$Transactions"
} }
1 Like

Thanks @steevej! It was simpler than I thought :slightly_smiling_face:

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.