How to count fields in Mongodb Aggregation

Question

I have a document with entries like this

{ 
    "_id": ObjectId("5644c495d0807a1750043237"),
    "siteid": "123456"
    "amount": 1.32
}

Some documents have other amounts eg."cashbackAmount"

I want a sum and a count for each amount fields. Not every document contains all the amount fields.

I hjave tried the following

{
    $group: {   
        "_id": "$siteid",
        item2: { "$sum": "$amount" },
        item3: { "$sum": "$totalAmount" },
        item4: { "$sum": "$cashbackAmount" },
        item5: { "$sum": "$unitPrice" },
    }
}

It gives me the sum, but I cannot work out how to get the number times each amount field is present.

{ "$sum": 1 } does not work because that gives me all the documents that have any one of the totals fields.

What do you mean by get the number times each amount field is present? Can you give some expected output example? — Shrabanee
– Shrabanee, Commented Jun 24, 2016 at 10:45

DAXaholic · Accepted Answer · 2016-06-24 11:32:18Z

1

I guess you probably want something like that

db.getCollection('amounts').aggregate([
    {
        $project: {
            siteid: 1,
            amount: 1,
            totalAmount: 1,
            unitPrice: 1,
            cashbackAmount: 1,
            amountPresent: {
                $cond: {
                    if: "$amount",
                    then: 1,
                    else: 0
                }
            },
            totalAmountPresent: {
                $cond: {
                    if: "$totalAmount",
                    then: 1,
                    else: 0
                }
            },
            cashbackAmountPresent: {
                $cond: {
                    if: "$cashbackAmount",
                    then: 1,
                    else: 0
                }
            },
            unitPricePresent: {
                $cond: {
                    if: "$unitPrice",
                    then: 1,
                    else: 0
                }
            }
        }
    },
    {
        $group: {   
            "_id": "$siteid",
            amountSum:   { "$sum": "$amount" },
            amountCount: { "$sum": "$amountPresent" },
            totalAmountSum:   { "$sum": "$totalAmount" },
            totalAmountCount: { "$sum": "$totalAmountPresent" },
            cashbackAmountSum:   { "$sum": "$cashbackAmount" },
            cashbackAmountCount: { "$sum": "$cashbackAmountPresent" },
            unitPriceSum:   { "$sum": "$unitPrice" },
            unitPriceCount: { "$sum": "$unitPricePresent" }
        }
    }
])

answered Jun 24, 2016 at 11:32

DAXaholic

36k6 gold badges84 silver badges77 bronze badges

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

2 Comments

DAXaholic Over a year ago

Then please up vote the answers which helped you (not only mine) :)

user3782299 Over a year ago

Will do when I get a reputation

chridam · Accepted Answer · 2016-06-24 11:03:15Z

If you know the amount fields in advance then you could do this in a single aggregation operation where you create the pipeline dynamically.

Check out the following demonstration:

var amountFields = ["amount", "totalAmount", "cashbackAmount", "unitPrice"],
    groupOperator = { "$group": { "_id": "$siteid" } };

amountFields.forEach(function (field){ 
    groupOperator["$group"][field+"Total"] = { "$sum": "$"+field }; 
    groupOperator["$group"][field+"Count"] = { 
       "$sum": {            
           "$cond": [ { "$gt": [ "$"+field, null ] }, 1, 0 ]
        }
    };
});

db.test.aggregate([groupOperator]);

Populate Test Documents

db.test.insert([
    { 
        "siteid": "123456",
        "amount": 1.32
    },
    { 
        "siteid": "123456",
        "cashbackAmount": 8.32
    },
    { 
        "siteid": "123456",
        "cashbackAmount": 9.74
    },
    { 
        "siteid": "123456",
        "unitPrice": 0.19
    },
    { 
        "siteid": "123456",
        "amount": 27.8,
        "totalAmount": 15.22,
        "unitPrice": 5.10,
        "cashbackAmount": 43.62    
    },
    { 
        "siteid": "123456",
        "unitPrice": 5.07
    },
    { 
        "siteid": "123456",
        "amount": 12.98,
        "totalAmount": 32.82
    },
    { 
        "siteid": "123456",
        "amount": 6.65,
        "unitPrice": 5.10
    }
])

Sample Aggregation Output

{
    "_id" : "123456",
    "amountTotal" : 48.75,
    "amountCount" : 4,
    "totalAmountTotal" : 48.04,
    "totalAmountCount" : 2,
    "cashbackAmountTotal" : 61.68,
    "cashbackAmountCount" : 3,
    "unitPriceTotal" : 15.46,
    "unitPriceCount" : 4
}

Thanks "$cond": [ { "$gt": [ "$"+field, null ] }, 1, 0 ] was the way to go

Collectives™ on Stack Overflow

How to count fields in Mongodb Aggregation

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related