Reputation: 11
I have a problem with sum detailed join with $graphLookUp
and $map
but the items retrieves double array :S, there is a way to group the elements in a sum?
This is an example of my collection:
/* 1 */
{
"type" : "Fase1",
"total" : 100,
"key" : "A6E19D93-8546-4A3A-8887-E23C301A290F",
},
/* 2 */
{
"type" : "Fase1",
"total" : 340.80,
"key": "85547A5B-623B-4408-9B3E-26F9B368B2C7"
},
/* 3 */
{
"type" : "Fase2",
"key": "F6307773-A6EF-41D4-A2F3-0FE55543846E"
"complement" : {
"payments" : [
{
"amount" : 20,
"documentsRelated" : [
{
"keyId" : "85547A5B-623B-4408-9B3E-26F9B368B2C7",
}
]
},
{
"amount" : 15,
"documentsRelated" : [
{
"keyId" : "85547A5B-623B-4408-9B3E-26F9B368B2C7",
}
]
}
],
},
}
I need to get the information in a list with the sum of type Phase2
/* 1 */
{
"type" : "Fase1",
"total" : 100,
"key" : "A6E19D93-8546-4A3A-8887-E23C301A290F",
},
/* 2 */
{
"type" : "Fase1",
"total" : 340.80,
"key": "85547A5B-623B-4408-9B3E-26F9B368B2C7",
//sum 20+35 from keyId related
"totalFase2": 35
}
there is my example code, I'm confused sum data in 1 column:
query = [
{
$match: {
type: 'Fase1',
},
}, {
$graphLookup: {
from: 'invoices',
startWith: '$key',
connectFromField: 'key',
connectToField: 'complement.payments.documentsRelated.keyId',
as: 'payments',
restrictSearchWithMatch: {
'type': 'Fase2',
},
},
},
{
$project: {
_id: 1,
type: 1,
total: 1,
paymentData: '$payments.complement.payments'
},
},
];
db.getCollection('invoices').aggregate(query);
Upvotes: 1
Views: 34
Reputation: 36134
Try $map
to iterate loop of payments.complement.payments.amount
array of array of amount, $sum
array of amount, $map
will return an array of number again $sum
array of amount,
{
$project: {
_id: 1,
type: 1,
total: 1,
totalFase2: {
$sum: {
$map: {
input: "$payments.complement.payments.amount",
in: { $sum: "$$this" }
}
}
}
}
}
Upvotes: 1