0

I am trying a mongo aggregate query with $lookup and $filter The functionality is fetching roles list along with the corresponding users count. Here there are a lot of users around 13k, in one role and it is throwing the error while executing that count inside the mongo query.

Query:

var aggregateObjArr = [
                        {$lookup: {
                                "localField": "_id",
                                "from": "Members",
                                "foreignField": "role",
                                "as": "membersInfo"
                            }
                        },
                        { $project: {
                                name: 1, preferredName: 1, isStaticRole: 1, createdBy: 1, privilegeCount: {$sum: {"$size": "$privilegeIds" }},
                                "membersInfo": {
                                    "$filter": {
                                        "input": "$membersInfo",
                                        "as": "child",
                                        "cond": { "$ne": ["$$child.status", -1]}
                                    }
                                }
                            }
                        },
                        { $project: {
                                name: 1, preferredName: 1, privilegeCount: 1, isStaticRole: 1, createdBy: 1, "userCount": { $sum: {"$size": "$membersInfo" }}
                            }
                        },
                        sortObj,
                        { $group: { _id: null, count: { $sum: 1 }, results: {'$push': '$$ROOT'}} },
                        { $project: { total: "$count", _id: 0, "results" : {$slice: ["$results", skipRows, limitRows]} }}
                    ];

collection.aggregate(aggregateObjArr, {"collation": {locale: "en", strength: 2}, "allowDiskUse" : true }, function (err, contentData) {

});

And it throws the following error:

{ MongoError: Total size of documents in Members matching { $match: { $and: [ { role: { $eq: ObjectId('58949cedc7428a346f097ff8') } }, {} ] } } exceeds maximum document size
    at Function.MongoError.create (/home/www/ssnw/v2/node_modules/mongodb-core/lib/error.js:31:11)
    at /home/www/ssnw/v2/node_modules/mongodb-core/lib/connection/pool.js:497:72
    at authenticateStragglers (/home/www/ssnw/v2/node_modules/mongodb-core/lib/connection/pool.js:443:16)
    at Connection.messageHandler (/home/www/ssnw/v2/node_modules/mongodb-core/lib/connection/pool.js:477:5)
    at Socket.<anonymous> (/home/www/ssnw/v2/node_modules/mongodb-core/lib/connection/connection.js:331:22)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    at TCP.onread (net.js:547:20)
  name: 'MongoError',
  message: 'Total size of documents in Members matching { $match: { $and: [ { role: { $eq: ObjectId(\'58949cedc7428a346f097ff8\') } }, {} ] } } exceeds maximum document size',
  ok: 0,
  errmsg: 'Total size of documents in Members matching { $match: { $and: [ { role: { $eq: ObjectId(\'58949cedc7428a346f097ff8\') } }, {} ] } } exceeds maximum document size',
  code: 4568,
  codeName: 'Location4568' }

I can't user the $match condition instead of $fliter since some roles exists without any users. Can anyone help?

Sanjay Kumar N S
  • 4,653
  • 4
  • 23
  • 38
  • The answer is to use `$unwind` right after the `$lookup` and actually `$match` instead of `$filter` on the array. The actual error message changed slightly with MongoDB 3.6 since `$lookup` now internally uses `$expr` for the matching. – Neil Lunn Apr 26 '18 at 21:31
  • But unwind with match will filter only the roles with at least one user rt. There can be roles without any users. – Sanjay Kumar N S Apr 27 '18 at 03:11
  • What you need to "understand" here is the error message says that you are matching so many users on "some" of the master detail collection that the resulting size of the array breaches the BSON limit. You can keep the "left join" with MongoDB 3.6, but even so you still have items that breach the limit before you `$filter`. I'll actually update the answer there to include the 3.6 detail, but it's still the basic approach. Cannot have more than 16MB as it's a "hard" limit with no exceptions. – Neil Lunn Apr 27 '18 at 03:16
  • Added the content to explain the usage. – Neil Lunn Apr 27 '18 at 03:37
  • 1
    Also, at any rate this line `{ $group: { _id: null, count: { $sum: 1 }, results: {'$push': '$$ROOT'}} }` is definitely going to break the BSON limit. If you want a "total count of results" then you run this in a separate query instead. You have not hit that problem yet, but that's the next one coming. Simply don't do that. – Neil Lunn Apr 27 '18 at 05:33
  • I am using mongo 3.4 version. as u said if this is the next coming issue, then what you suggest for getting the total count? As a separate query? – Sanjay Kumar N S Apr 27 '18 at 05:36

0 Answers0