CODEWITHSUNDEEP
mongodbaggregation-framework
mo_maat
mo_maat

Reputation: 2240

mongodb multiple key aggregation

I have a sql background and understand the syntax for aggregating data in mongodb, but I'm having trouble with "flattening" the output of a multiple key aggregation in MongoDB. For example, the standard syntax is the following:

db.transactions.aggregate(
    [
     { $group: { _id: {"category":"$category","postdate":"$postdate"} , "total": { $sum: "$total" } } }
    ]
);

But this returns the data in the following form: 

[{"_id":{"category":"Fees","postdate":"2013-01-04T05:00:00.000Z"},"total":24},
{"_id":{"category":"Fees","postdate":"2012-12-20T05:00:00.000Z"},"total":-0.02}]

What I want is the data in a format like the following where I'm still grouping on two or more columns:

Option 1:

[{"_id":"Auto","postdate":"2013-01-04T05:00:00.000Z","total":24},
{"_id":"Fees","postdate":"2012-12-20T05:00:00.000Z","total":-0.02}]

Option 2:

["category":"Auto","postdate":"2013-01-04T05:00:00.000Z","total":24},
{"category":"Fees","postdate":"2012-12-20T05:00:00.000Z","total":-0.02}]

How do I do this in mongodb?

Upvotes: 1

Views: 126

Answers (1)

s7vr
s7vr

Reputation: 75984

Format your response using $project

Option 1:

db.transactions.aggregate(
        [
         { $group: { _id: {"category":"$category","postdate":"$postdate"} , "total": { $sum: "$total" } } },
         { $project: { "_id":0, "_id":"$_id.category", "postdate":"$_id.postdate", "total":1 } }
        ]
);

Option 2:

db.transactions.aggregate(
        [
         { $group: { _id: {"category":"$category","postdate":"$postdate"} , "total": { $sum: "$total" } } },
         { $project: { "_id":0, "category":"$_id.category", "postdate":"$_id.postdate", "total":1 } }
        ]
);

Upvotes: 4

Related Questions