Hi guys,
I am fairly new to MongoDB so there might be obvious mistakes here. I have attemted an aggregation pipeline which takes several seconds for a mere collection size of ~2000 documents. I have isolated the bottleneck: the initial $lookup step. Hints are welcome, you are also welcome to send me your contact details if you do consulting around these kind of issues on a regular basis.
I am trying to do something similar to the SQL statement
SELECT t.* FROM ModelEvaluation t
WHERE t.id = (SELECT t1.id FROM ModelEvaluation t1
WHERE t1.customer_id = t.customer_id
ORDER BY t1.modified DESC LIMIT 1)
My Mongo aggregation (the part that takes +90% of the time)
db.ModelEvaluation.aggregate([
{
$lookup: {
from: "ModelEvaluation",
let: {f_customer_id: "$customer_id"},
pipeline: [
{
/* This looks complex but it has to be this way.
*
* https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#lookup-join-let */
$match: {
$expr: {
$eq: ["$customer_id", "$$f_customer_id"],
}
}
},
{$sort: {modified: -1}},
{$limit: 1},
{$project: {_id: true}}
],
as: "c1",
}
},
{$out: "foo"},
])