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 ?

Sums up the defined value from all documents in the collection: Example : db.COLLECTION_NAME.aggregate(AGGREGATE_OPERATION) — helpdoc
– helpdoc, Commented May 24, 2017 at 11:19
@SNTiwari im not sure I understand what you want to do there? — torbenrudgaard
– torbenrudgaard, Commented May 24, 2017 at 11:52

Neil Lunn · Accepted Answer · 2017-05-24 12:17:00Z

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"
           ]
         }
       }
     }
   }}
])

edited May 24, 2017 at 12:17

answered May 24, 2017 at 11:51

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

torbenrudgaard Over a year ago

Im not sure I understand it, but im gonna try it for sure :)

torbenrudgaard Over a year ago

YES!!! It worked like a charm!! Thank you so much for showing me how to do this. Just to try and understand, you first map the invoice records, and inside those you map the lines from the invoice. I would never have thought of this :)

Neil Lunn Over a year ago

@torbenrudgaard No problem. If you can use a latest MongoDB 3.4 the $reduce operator makes it a "little" less terse and possibly a bit clearer.

torbenrudgaard Over a year ago

Ya, $reduce is pretty powerful!

s7vr · Accepted Answer · 2017-05-24 12:05:05Z

1

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"] } 
           }
        }
   }}

answered May 24, 2017 at 12:05

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

2 Comments

torbenrudgaard Over a year ago

Hi again @Veeram - as usual you come up with good solutions!! I was thinking of using $concat at first, but had no idea how to make it work. Thanks for explaining $reduce, I can use that one in many of my other aggregates, What does the double $$ signs mean? Im not sure about that one.

s7vr Over a year ago

Its notation to access aggregation variables. More here docs.mongodb.com/manual/reference/aggregation-variables and docs.mongodb.com/manual/reference/operator/aggregation/reduce/…

Collectives™ on Stack Overflow

Sum of multiple lines inside nested arrays with aggregate

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related