Hi @Gary_Tam,
I’m not sure I understood what you want as I don’t understand the example but here is my try:
Here is the pipeline I’m using in my example:
[
{
'$facet': {
'all': [
{
'$group': {
'_id': null,
'all_docs': {
'$push': '$$ROOT'
}
}
}
],
'sumA': [
{
'$group': {
'_id': '$a',
'sum': {
'$sum': '$value'
}
}
}
],
'sumB': [
{
'$group': {
'_id': '$b',
'sum': {
'$sum': '$value'
}
}
}
]
}
}
]
Here is my complete example:
test [direct: primary] test> db.coll.insertMany([{a:1, b:1, value: 4}, {a:2, b:1, value: 5}, {a:1, b:2, value: 7}, {a:2, b:2, value: 2}])
{
acknowledged: true,
insertedIds: {
'0': ObjectId("61644cf84d20c6f63d7944a0"),
'1': ObjectId("61644cf84d20c6f63d7944a1"),
'2': ObjectId("61644cf84d20c6f63d7944a2"),
'3': ObjectId("61644cf84d20c6f63d7944a3")
}
}
test [direct: primary] test> db.coll.aggregate([
... {
..... '$facet': {
....... 'all': [
....... {
......... '$group': {
........... '_id': null,
........... 'all_docs': {
............. '$push': '$$ROOT'
............. }
........... }
......... }
....... ],
....... 'sumA': [
....... {
......... '$group': {
........... '_id': '$a',
........... 'sum': {
............. '$sum': '$value'
............. }
........... }
......... }
....... ],
....... 'sumB': [
....... {
......... '$group': {
........... '_id': '$b',
........... 'sum': {
............. '$sum': '$value'
............. }
........... }
......... }
....... ]
....... }
..... }
... ])
[
{
all: [
{
_id: null,
all_docs: [
{
_id: ObjectId("61644cf84d20c6f63d7944a0"),
a: 1,
b: 1,
value: 4
},
{
_id: ObjectId("61644cf84d20c6f63d7944a1"),
a: 2,
b: 1,
value: 5
},
{
_id: ObjectId("61644cf84d20c6f63d7944a2"),
a: 1,
b: 2,
value: 7
},
{
_id: ObjectId("61644cf84d20c6f63d7944a3"),
a: 2,
b: 2,
value: 2
}
]
}
],
sumA: [ { _id: 1, sum: 11 }, { _id: 2, sum: 7 } ],
sumB: [ { _id: 1, sum: 9 }, { _id: 2, sum: 9 } ]
}
]
Cheers,
Maxime.