$group after $lookup is taking way too long

Question

I have following mongo collection:

{
    "_id" : "22pTvYLd7azAAPL5T",
    "plate" : "ABC-123",
    "company": "AMZ",
    "_portfolioType" : "account"
},
{
    "_id" : "22pTvYLd7azAAPL5T",
    "plate" : "ABC-123",
    "_portfolioType" : "sale",
    "price": 87.3
},
{
    "_id" : "22pTvYLd7azAAPL5T",
    "plate" : "ABC-123",
    "_portfolioType" : "sale",
    "price": 88.9
}

And I am trying to aggregate all documents which have same value in plate field. Below is the query I have written so far:

db.getCollection('temp').aggregate([
{
    $lookup: { 
        from: 'temp',
        let: { 'p': '$plate', 't': '$_portfolioType' },
        pipeline: [{
            '$match': {
                '_portfolioType': 'sale',
                '$expr': { '$and': [ 
                    { '$eq': [ '$plate', '$$p'  ] },
                    { '$eq': [ '$$t', 'account'  ] }
                ]}
            }
        }],
        as: 'revenues' 
    },
},
{
    $project: {
        plate: 1,
        company: 1,
        totalTrades: { $arrayElemAt: ['$revenues', 0] },
    },
},

{
    $addFields: {
        revenue: { $add: [{ $multiply: ['$totalTrades.price', 100] }, 99] },
    },
},

{
    $group: {
        _id: '$company',
        revenue: { $sum: '$revenue' },
    }
}
])

Query works fine if I remove $group stage, however, as soon as I add $group stage mongo starts an infinite processing. I tried adding $match as the first stage so to limit number of documents to process but without any luck. E.g:

{
    $match: { $or: [{ _portfolioType: 'account' }, { _portfolioType: 'sale' }] }
},

I also tried using { explain: true } but it doesn't return anything helpful.

It "works fine" because it returns you a cursor as soon as first batch of documents is available. $group is a blocking stage. It requires all previous stages to finish. The $lookup doesn't look right. What are you trying to do there? — Alex Blex, May 15 '18 at 17:20
Could you provide what is the end result required . The statement written "trying to aggregate all documents which have same value in plate field " is not very intuitive. — mintekhab, May 15 '18 at 17:22
ok, my end goal is to get document for each "company" (company field), with count of `plate` it has (i.e. number of documents for a company with unique `plate` and a calculated revenue field which basically is: get all documents with `_portfolioType: sale` associated with each of the `plate` — Waqas, May 16 '18 at 00:39
I was just about to bang on about indexes until I realized you are doing a "self join" back to the same collection. You likely don't need to do that. Take a look at [Aggregation filter after $lookup](https://stackoverflow.com/q/36459983/2313887), where "despite the question title" the actual solution to that particular problem ( where the OP was also performing a "self join" ) actually does not involve `$lookup` at all. You're likely asking something very similar, and if you're not sure then you should show some documents and an expected result as was asked for. — Neil Lunn, May 16 '18 at 02:49

score 1 · Accepted Answer · answered May 16 '18 at 09:45

As Neil Lunn noticed, you very likely don't need the lookup to reach your "end goal", which is still quite vague.

Please read comments and adjust as needed:

db.temp.aggregate([
    {$group:{
        // Get unique plates
        _id: "$plate",
        // Not clear what you expect if there are documents with
        // different company, and the same plate.
        // Assuming "it never happens"
        // You may need to $cond it here with {$eq: ["$_portfolioType", "account"]}
        // but you never voiced it.         
        company: {$first:"$company"},
        // Not exactly all documents with _portfolioType: sale,
        // but rather price from all documents for this plate.
        // Assuming price field is available only in documents 
        // with "_portfolioType" : "sale". Otherwise add a $cond here.
        // If you really need "all documents", push $$ROOT instead.
        prices: {$push: "$price"}        
    }},
    {$project: {
       company: 1,
       // Apply your math here, or on the previous stage
       // to calculate revenue per plate
       revenue: "$prices" 
    }}
    {$group: {
        // Get document for each "company" 
        _id: "$company",
        // Revenue associated with plate
        revenuePerPlate: {$push: {"k":"$_id", "v":"$revenue"}}        
    }},
    {$project:{         
        _id: 0,
        company: "$_id",
        // Count of unique plate
        platesCnt: {$size: "$revenuePerPlate"},
        // arrayToObject if you wish plate names as properties
        revenuePerPlate: {$arrayToObject: "$revenuePerPlate"}
    }}
])

$group after $lookup is taking way too long

1 Answers1