Sum of multiple lines inside nested arrays with aggregate

Question

I got a booking, which have several invoices, each invoice have several invoiceLines.

I want my aggregate to select bookings joined with invoices, and then just give me the total for all invoiceLines.amountTotal.

Example booking table:

{ 
    "_id" : "0PDLR", 
    "checkin" : 1488326400, 
    "checkout" : 1498780800, 
}

Example invoice table:

{ 
    "_id" : 1, 
    "bookingId" : "0PDLR", 
    "invoiceLines" : [
        {
            "lineText" : "Rent Price", 
            "amountTotal" : 3000
        }, 
        {
            "lineText" : "Discount", 
            "amountTotal" : -500
        }, 
    ] 
}   
{ 
    "_id" : 2, 
    "bookingId" : "0PDLR", 
    "invoiceLines" : [
        {
            "lineText" : "Final cleaning", 
            "amountTotal" : 200
        }, 
    ] 
}   
{ 
    "_id" : 3, 
    "bookingId" : "0PDLR", 
    "invoiceLines" : [
        {
            "lineText" : "Taxi to Airport", 
            "amountTotal" : 300
        }, 
        {
            "lineText" : "Reservation fee paid already", 
            "amountTotal" : -500
        }
    ] 
}   

This is the result I would like to get:

booking Result: [
    {
        "_id": "0PDLR",
        "checkin": 1488326400,
        "checkout": 1498780800,
        "sum": 2500
    }
]

This is my current aggregate pipeline:

    bookingTable.aggregate([
        { "$match": myMatch },
        {                                                                 
            $lookup: {                                                    
                from: "invoice",                                          
                localField: "_id",                                        
                foreignField: "bookingId",                                
                as: "invoice"                                             
            }                                                             
        },                                                                
        {                                                                  
            $project:{                                                     
                "_id" : 1,                                                
                "checkin" : 1,                                            
                "checkout" : 1,                                           
                "invoiceLines" : "$invoice.invoiceLines"                  
            }                                                              
        }                                                                  
    ])

Which of cause just returns a booking with all the invoice lines one by one,
so I will have to to the usual:

    var invoiceTotal = 0;
    for (var i=0; i<selectBooking[b].invoiceLines.length; i++) {
        for (var x=0; x<selectBooking[b].invoiceLines.length; x++) {    
            for (var y=0; y<selectBooking[b].invoiceLines[x].length; y++) { 
                invoiceTotal += selectBooking[b].invoiceLines[x][y].amountTotal
            }
        }
    }

So my question is, can I do this in MongoDB instead?

Is there a way to make it count all the invoiceLines.amountTotal and then just return a sum in the $project ?

Answer 1

This requires MongoDB 3.2 at least, but you can do this using $map and $sum

 bookingTable.aggregate([
   { "$match": myMatch },
   { "$lookup": {
     "from": "invoice",
     "localField": "_id",
     "foreignField": "bookingId",
     "as": "invoice"
   }},
   { "$project": {
     "checkin" : 1,
     "checkout" : 1,
     "bookingTotal": {
       "$sum": {
         "$map": {
           "input": "$invoice",
           "as": "iv",
           "in": {
             "$sum": {
               "$map": {
                 "input": "$$iv.invoiceLines",
                 "as": "il",
                 "in": "$$il.amountTotal"
               }
             }
           }
         }
       }
     }
   }}
])

Which is really just iterating through each array and reducing the result down to a single value for each line from the "amountTotal".

With 3.4 you can make that a little less terse with $reduce:

 bookingTable.aggregate([
   { "$match": myMatch },
   { "$lookup": {
     "from": "invoice",
     "localField": "_id",
     "foreignField": "bookingId",
     "as": "invoice"
   }},
   { "$project": {
     "checkin" : 1,
     "checkout" : 1,
     "bookingTotal": {
       "$reduce": {
         "input": "$invoice",
         "initialValue": 0,
         "in": {
           "$sum": [
             { "$reduce": {
               "input": "$$this.invoiceLines",
               "initialValue": 0,
               "in": { "$sum": [ "$$this.amountTotal", "$$value" ] }
             }},
             "$$value"
           ]
         }
       }
     }
   }}
])

Answer 2

You can try the $reduce with $concatArrays in 3.4 version.

Think of amountTotal as [[3000, -500], [200], [300, -500]]. Inner $reduce to transform amountTotal to [3000, -500, 200, 300, -500] followed by outer $reduce to sum the amounts.

 { "$addFields": {
     "invoiceTotal": 
             {
           $reduce: {
              input: {
               $reduce: {
                  input: "$invoice.invoiceLines",
                  initialValue: [],
                  in: { $concatArrays : ["$$value", "$$this.amountTotal"] }
               }
             },
              initialValue: 0,
              in: { $add : ["$$value", "$$this"] } 
           }
        }
   }}