MongoDB Group by field and show array of grouped items?

Question

I have a collection of Projects in where projects are like this:

{
    "_id" : ObjectId("57e3e55c638cb8b971"),
    "allocInvestor" : "Example Investor",
    "fieldFoo" : "foo bar",
    "name" : "GTP 3 (Roof Lease)"
}

I want to receive a list of projects grouped by allocInvestor field and only show fields: name and id

If I use aggregate and $group like this:

db.getCollection('projects').aggregate([
    {"$group" : {
                    _id:"$allocInvestor", count:{$sum:1}
                }
    }
])

I receive a count of project per allocInvestor but I need is to receive the list of allocInvestor with subarray of projects per allocInvestor.

I'm using meteor by the way in case that helps. But I first want to get the query right on mongodb then try for meteor.

4J41 · Answer 1 · 2016-10-04T15:01:03.537

You can use $push or $addToSet to create a list of name and _id per every group. $push allows duplicates and $addToSet does not add an element to the list again, if it is already there.

db.getCollection('projects').aggregate([
                                            { "$group" : { _id : "$allocInvestor", 
                                                           count : {$sum : 1},
                                                           "idList" : {"$addToSet" : "$_id"}, 
                                                           "nameList" : {"$addToSet":"$name"}
                                                       }     
                                            } 
                                    ]);

To get the name and _id data in a single list:

db.getCollection('projects').aggregate([ 
                                            { "$group" : { _id : "$allocInvestor", "projects" : {"$addToSet" : {id : "$_id", name: "$name"}}}}, 
                                            {"$project" : {"_id" : 0, allocInvestor : "$_id", "projects" : 1 }}
                                        ]);

is there anyway to receive a list back such as: {allocInvestor: alloc1, projects: [ {id:"1", name:"proj1"},{id:2, name: "proj2"}], allocInvestor:alloc2, projects:[...]} — CommonSenseCode, Oct 04 '16 at 14:39

score 0 · Answer 2 · answered Oct 04 '16 at 19:14

Use the $$ROOT operator to reference the entire document and then use project to eliminate the fields that you do not require.

db.projects.aggregate([
    {"$group" : {
                    "_id":"$allocInvestor", 
                    "projects" : {"$addToSet" : "$$ROOT"}
                }
    },
    {"$project" : {
                     "_id":0,
                     "allocInvestor":"$_id",
                     "projects._id":1
                     "projects.name":1
                  }
    }
])

MongoDB Group by field and show array of grouped items?

2 Answers2