0

I am trying to generate a users report which is basically a fetch of all the users in the system and add to that the total number of "orders" and "projects" each users has. This query below works perfectly when users don't have much orders/projects but if some user has lots of projects I get error:

Total size of documents in projects matching { $match: { $and: [ { owner: { $eq: ... } }, {} ] } } exceeds maximum document size

The query is:

db.getCollection('users').aggregate([
    {$match: {}},
    {$skip: 0},
    {$limit: 50},
    {$lookup: {from: 'projects', localField: '_id', foreignField: 'owner', as: 'projects'}},
    {$addFields: {projects: {$size: "$projects"}}},
    {$lookup: {from: 'orders', localField: '_id', foreignField: 'user', as: 'orders'}},
    {$addFields: {orders: {$size: "$orders"}}}
])

Is there any workaround to that? I know I can probably use "unwind" and "group" steps but no sure how it will work with BOTH orders and projects.

MongoDB version 3.4

Thanks.

ValYouW
  • 669
  • 2
  • 9
  • 20
  • `$group` will of course change the order, but you also notably do not `$sort` anywhere in the pipeline at all. If you expect a certain order then you need to ad `$sort` after each `$group` is processed, possibly adding other fields via `$first` to get the common values after `$unwind`. But as noted in the duplicate, the `$unwind` is actually a "requirement" with large results that would exceed the BSON limit. From MongoDB 3.6 you could actually return the "count" as the result of `$lookup` instead. When that becomes available. – Neil Lunn Sep 03 '17 at 13:59
  • Thanks @NeilLunn Yeah, I saw the dup question before opening this, I thought maybe there is some way I can avoid the $unwind and $group after each $lookup, especially because $group removes all the fields and I'll have to re-add them using $first for each field :( I do need to add $sort, where would be the best place for that? after the last $group? – ValYouW Sep 03 '17 at 14:29
  • Well if you actually did read it then you would understand why you cannot avoid it. I did actually go into some detail about why this is the case there. `$unwind` comes after `$lookup` "immediately", and then `$group` after that. You can probably get away with one `$sort` at the very end of the pipeline. – Neil Lunn Sep 03 '17 at 14:32
  • Thanks, I'll give it a shot and thanks for the headsup reagarding mongo 3.6 – ValYouW Sep 03 '17 at 14:55

0 Answers0