CODEWITHSUNDEEP
arraysmongodbmongodb-queryaggregation-frameworklookup
user2829319
user2829319

Reputation: 319

Mongo "Error in $cursor stage..." in aggregate pipeline

I have the collection orders

{_id: ObjectId("5e32d0fadf54cb3d81293fb3"),
user_id:'test_user',
product_id:'product_2_id'
created_at:'2020-02-25T07:04:05.439+00:00'
}

that I need to join with the collection recommendedProducts having as common key the orders.user_id with the recommendedProducts.userId that looks like

{_id: ObjectId("5e3ac145df54cbeca6230924"),
userId:'test_user'
rankedList:[
0:{Name:'Product_4',id:'product_4_id'},
1:{Name:'Product_1',id:'product_1_id'},
2:{Name:'Product_2',id:'product_2_id'}],
Date:'2020-02-25T06:03:55.439+00:00'
}

{_id: ObjectId("5e388da4df54cb8efb47e61b"),
userId:'test_user'
rankedList:[
0:{Name:'Product_5',id:'product_5_id'},
1:{Name:'Product_6',id:'product_6_id'},
2:{Name:'Product_3',id:'product_3_id'}],
Date:'2020-02-25T05:03:55.439+00:00'
}

I have read this and also this and that posts but I am still not able to sort out how the aggregate pipeline should be written properly. So far I have the below

db.orders.aggregate([
 {
    $lookup: {
        "from": "recommendedProducts",
        "as": "recommendations",
        "let": {
            "id": "$user_id"
        },
        "pipeline": [{
                "$match": {
                    "$expr": {
                        "$eq": ["$userId", "$$id"]
                    }
                }
            },
            {
                "$sort": {
                    "Date": -1
                }
            },
            {
                "$limit": 1
            }
        ]
    }
}
])

which should have worked properly but no success. What I need to keep here is the last doc from the collection recommendedProducts, and only one doc from there, this is why I use sort and limit also but I get the error "Error in $cursor stage :: caused by :: operation exceeded time limit" Where is the error here?

Upvotes: 1

Views: 5368

Answers (1)

Joe
Joe

Reputation: 28326

When using the form of $lookup that takes let and pipeline, and also when using $expr, the query planner is currently not able to use efficiently use an index.

This means that for each order, the lookup stage is scanning the entire recommendedProducts collection to find the matching documents.

If you were to use the other form of lookup, and have an index on userId field in the recommendedProducts collection, it would be able to use that index to find the matching products more quickly, and you could then use unwind, sort, and group, or just reduce to get the most recent value.

db.orders.aggregate([
 {
    $lookup: {
        "from": "recommendedProducts",
        "as": "recommendations",
        "localField": "user_id",
        "foreignField": "userId"
 },
 {$addFields: {
   recommendations:{ 
        $reduce: {
            input: "$recommendations",
            initialValue: {$arrayElemAt:["$recommendations",0]},
            in: {
               $cond: {
                    if: {$gt:["$$this.Date","$$value.Date"]},
                    then: "$$this",
                    else: "$$value"
                }
            }
        }
   }
 }}
])

Upvotes: 3

Related Questions