将MongoDB聚合$ lookup限制为仅1个匹配项 [英] Limit MongoDB aggregation $lookup to only 1 match

问题描述

有两个集合，分别是 users 和 reports .

There are two collections, users and reports.

我的目标是进行汇总，以获取所有用户，并为每个 user 包含用户 last report 的数量.

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

这是我当前的汇总:

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

此查询正常运行，但速度非常慢.
原因是 $ lookup 返回与某个 userId 匹配的所有报告，而不是一个(最后一个).

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).

是否可以将 $ lookup 限制为一个匹配项?

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

推荐答案

您可以尝试3.6中提供的新查找变体.

You can try the new lookup variant available in 3.6.

在报表集合的userId和date字段上添加索引，看看它是否被拾取.

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

这篇关于将MongoDB聚合$ lookup限制为仅1个匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！