MongoDB aggregation count

Question

I have collections Users and Numbers. Each number has AllocatedToUserId (ObjectId) field, User can have many numbers. How can I in single query return User with NumbersCount? I wrote this

db.getCollection('Users').aggregate([
      {'$lookup': {from: "Numbers",
                   localField: "_id",
                   foreignField: "AllocatedToUserId",
                   as: "Numbers"}
      },{ '$project' : {_id:0,
                       document: '$$ROOT',
                       count: {$size:'$Numbers'}}}
      ])

But with this code I got structure of response like that:

[{document, count},{document, count},{document, count}...]

Where each document has Numbers collection, and I'd like to have count inside document and without collection of numbers. Is it possible? Thanks!

chridam · Accepted Answer · 2016-10-12 14:47:58Z

1

You don't necessarily need to project another field with the $$ROOT system variable, just project the fields from the users schema. The following example assumes you have a user with the schema:

Exmple Users Schema

{
    "_id": ObjectId
    "firstName": String,
    "lastName": String,
    "age": Integer,
    "location": String
}

Pipeline

db.getCollection('Users').aggregate([
    {
        "$lookup": {
            "from": "Numbers",
            "localField": "_id",
            "foreignField": "AllocatedToUserId",
            "as": "Numbers"
        }
    },
    {   
        "$project": {
            "firstName": 1,
            "lastName": 1,
            "age": 1,
            "location": 1,
            "Numbers": 0,
            "count": { "$size": "$Numbers" } 
        }
    }
])

answered Oct 12, 2016 at 14:47

chridam

104k26 gold badges246 silver badges243 bronze badges

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

4 Comments

Nikita Pedorich Over a year ago

Thanks! Yep, probably that's a solution, but my user schema has ~25 fields. That's why I used $$ROOT.

chridam Over a year ago

With $$ROOT you were still going to have another $project pipeline to flatten the document embedded document

Nikita Pedorich Over a year ago

So.. That's mean that's, probably, best solution?

chridam Over a year ago

Unless if it's a strict requirement to have a flattened schema as a result I'm afraid you may have to $project every field otherwise the $$ROOT is a better solution since you don't have to explicitly project the fields.

Collectives™ on Stack Overflow

MongoDB aggregation count

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related