Limit MongoDB aggregation $lookup to only 1 match

Question

There are two collections, users and reports.

My goal is to make an aggregation that gets all users and for each user includes the amount of user's last report.

Here is my current aggregation:

db.users.aggregate([{
  $lookup: {
    from: 'reports',
    localField: '_id',
    foreignField: 'userId',
    as: 'report',
  },
}, {
  $project: {
    'lastReportAmount': {
      $let: {
        vars: {
          lastReport: {'$arrayElemAt': ['$report', 0]},
        },
        in: '$$lastReport.amount',
      },
    },
    'id': '$_id',
    'name': 1,
    'firstLogin': 1,
    'email': 1,
  },
}])

This query works correctly, BUT it's very slow.
The reason for that is that $lookup returns all reports that match a certain userId, instead of one (last).

Is there a way to limit the $lookup to only one match?

Answer 1

You can try the new lookup variant available in 3.6.

Add an index on the userId and date field in reports collection and see if it gets picked up.

db.users.aggregate([
 {"$lookup":{
    "from": "reports",
    "let": {"_id":"$_id"},
    "pipeline":[
      {"$match":{"$expr":{"$eq":["$$_id","$userId"]}}},
      {"$sort":{"date": -1}},
      {"$limit":1}
    ],
    "as": "lookup-latest"
 }},
 {"$project": {
    'lastReportAmount':{'$arrayElemAt':['$lookup-latest', 0]},
    'id': '$_id',
    'name': 1,
    'firstLogin': 1,
    'email': 1
 }}
])