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: