MongoDB and aggregation functions

Question

I've started playing with MongoDB and aggregation framework. What I'm trying to calculate is total value of expenses(333) and total value of subsistence(777). Any idea how that could be done? I've been playing with $unwind, $group, $project but got nowhere.

{
"_id" : ObjectId("5442a4afc25282ab96463126"),
"expenses" : [
    {
        "name" : "a",
        "expenseValue" : 111
    },
    {
        "name" : "b",
        "expenseValue" : 222
    }
],
"subsistence" : [
    {
        "name" : "a",
        "valueSubsistence" : 333
    },
    {
        "name" : "b",
        "subsistenceValue" : 444
    }
]

}

I got that working but had to fire two queries. One to unwind expenses, another one to unwind subsistence

db.test.aggregate([{$unwind:"$expenses"},{$group:{_id:null,totalExpenses:{$sum:"$expenses.expenseValue"}}}])
{
"result" : [
    {
        "_id" : null,
        "totalExpenses" : 333
    }
],
"ok" : 1

}

> db.test.aggregate([{$unwind:"$subsistence"},{$group:{_id:null,totalSubsistence:{$sum:"$subsistence.subsistenceValue"}}}])
{
"result" : [
    {
        "_id" : null,
        "totalSubsistence" : 777
    }
],
"ok" : 1

}

The thing is I want to get that done in one query. When I unwind two arrays in one go I'm getting below, and from there I got nowhere so far

db.test.aggregate([{$unwind:"$expenses"},{$unwind:"$subsistence"}])
{
"result" : [
    {
        "_id" : ObjectId("5442a4afc25282ab96463126"),
        "expenses" : {
            "name" : "a",
            "valueExpenses" : 111
        },
        "subsistence" : {
            "name" : "a",
            "valueSubsistence" : 555
        }
    },
    {
        "_id" : ObjectId("5442a4afc25282ab96463126"),
        "expenses" : {
            "name" : "a",
            "valueExpenses" : 111
        },
        "subsistence" : {
            "name" : "b",
            "valueSubsistence" : 444
        }
    },
    {
        "_id" : ObjectId("5442a4afc25282ab96463126"),
        "expenses" : {
            "name" : "b",
            "valueExpenses" : 111
        },
        "subsistence" : {
            "name" : "a",
            "valueSubsistence" : 333
        }
    },
    {
        "_id" : ObjectId("5442a4afc25282ab96463126"),
        "expenses" : {
            "name" : "b",
            "valueExpenses" : 222
        },
        "subsistence" : {
            "name" : "b",
            "valueSubsistence" : 444
        }
    }
],
"ok" : 1
}

JohnnyHK · Accepted Answer · 2014-10-18 22:25:34Z

You're mixing up your field names in your examples between styles of valueSubsistence and subsistenceValue, but assuming those are just typos and they're actually consistent, you're pretty close. The trick is to do two separate $group stages where you carry forward the results of the first $group into the second using $first.

So something like this:

db.test.aggregate([
    {$unwind: "$expenses"},
    {$group:{
        _id:null,
        totalExpenses: {$sum: "$expenses.expenseValue"},
        subsistence: {$first: '$subsistence'}
    }},
    {$unwind: "$subsistence"},
    {$group:{
        _id:null,
        totalExpenses: {$first: '$totalExpenses'},
        totalSubsistence: {$sum: "$subsistence.subsistenceValue"}
    }},
])

Which gives output of:

{
    "result" : [ 
        {
            "_id" : null,
            "totalExpenses" : 333,
            "totalSubsistence" : 777
        }
    ],
    "ok" : 1
}

Collectives™ on Stack Overflow

MongoDB and aggregation functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related