CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Jacek Wojcik
Jacek Wojcik

Reputation: 1253

Sum of two fields based on some conditions

These are my documents in Stat collection: 

{placeid: 'plaza', guestid: "xxx", logtype: "purchase", value: 12}
{placeid: 'plaza', guestid: "xxx", logtype: "visit", value: 0}
{placeid: 'plaza', guestid: "xxx", logtype: "purchase", value: 17}
{placeid: 'plaza', guestid: "yyy", logtype: "visit", value: 0}

I want to aggregate these documents to get information (passed plaza as argument):

This is my approach: 

Stat.aggregate(
        [
            { $match: { placeid: "plaza" } },
            {
                $group: {
                    _id: "$guestid",
                    totallogs: { $sum: 1 },
                    totalvalue: { $sum: "$value" },
                }
            }
        ]
)

problem here is that this aggregation does not take logtype into consideration.

And I do not know how to improve it. Any help?

Upvotes: 4

Views: 1623

Answers (1)

Sede
Sede

Reputation: 61225

You need to use the aggregation framework to $group your documents by "guestid" and use the $sum accumulator operator to return the sum. Of course you also need the $cond operator evaluates the value of "logtype" and returns the value of the "true case".

db.Stat.aggregate([
    { "$match": { "placeid": "plaza" } },
    { "$group": { 
        "_id": "$guestid", 
        "visit": { 
            "$sum": { 
                "$cond": [ 
                    { "$eq": [ "$logtype", "visit" ] }, 
                    1, 
                    0 
                ] 
            } 
         }, 
         "purchases": { 
             "$sum": { 
                  "$cond": [ 
                      { "$eq": [ "$logtype", "purchase" ] }, 
                      1, 
                      0 
                  ] 
             } 
        }, 
        "value_purchase": { 
            "$sum": {
                "$cond": [ 
                    { "$eq": [ "$logtype",   "purchase" ] },
                    "$value", 
                    0 
                ]  
            } 
        }  
    }}
])

which produces:

{ "_id" : "yyy", "visit" : 1, "purchases" : 0, "value_purchase" : 0 }
{ "_id" : "xxx", "visit" : 1, "purchases" : 2, "value_purchase" : 29 }

Upvotes: 6

Related Questions