CODEWITHSUNDEEP
mongodbmeteormongodb-queryaggregation-frameworkmongodb-aggregation
CommonSenseCode
CommonSenseCode

Reputation: 25369

MongoDB Group by field and show array of grouped items?

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.

Upvotes: 1

Views: 3603

Answers (2)

Anirudh Srinivas Madhavan
Anirudh Srinivas Madhavan

Reputation: 131

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
                  }
    }
])

Upvotes: 0

4J41
4J41

Reputation: 5095

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 }}
                                        ]);

Upvotes: 2

Related Questions