CODEWITHSUNDEEP
mongodbaggregation-framework
Michael
Michael

Reputation: 16142

Limit MongoDB aggregation $lookup to only 1 match

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?

Upvotes: 6

Views: 3906

Answers (1)

s7vr
s7vr

Reputation: 75934

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
 }}
])

Upvotes: 7

Related Questions