MongoDB聚合管道多关联查询：获取指定用户未浏览视频列表

需求说明 #

需要获取指定用户在analytics集合中没有video_impression记录的视频，同时视频需满足status: "complete"且privacy: "public"。

数据结构 #

db={
  "videos": [
    {
      "_id": "1",
      "name": "1's Video",
      "status": "complete",
      "privacy": "public"
    },
    {
      "_id": "2",
      "name": "2's Video",
      "status": "complete",
      "privacy": "public"
    },
    {
      "_id": "3",
      "name": "3's Video",
      "status": "complete",
      "privacy": "public"
    },
    {
      "_id": "4",
      "name": "4's Video",
      "status": "complete",
      "privacy": "private"
    },
    {
      "_id": "5",
      "name": "5's Video",
      "status": "flagged",
      "privacy": "public"
    }
  ],
  "analytics": [
    {
      "_id": "1",
      "user": "1",
      "event": "video_impression",
      "data": {
        "video": "1"
      }
    },
    {
      "_id": "2",
      "user": "2",
      "event": "video_impression",
      "data": {
        "video": "2"
      }
    }
  ]
}

原代码问题 #

原聚合管道仅做全局匹配，关联了所有用户的video_impression记录，最终返回的是所有用户都未浏览的视频，而非指定用户未浏览的视频：

db.videos.aggregate([
  {
    $match: {
      "status": "complete",
      "privacy": "public"
    }
  },
  {
    $lookup: {
      from: "analytics",
      localField: "_id",
      foreignField: "data.video",
      as: "matched_docs"
    }
  },
  {
    $match: {
      "matched_docs": {
        $eq: []
      }
    }
  }
])

解决方案 #

方法1：带条件过滤的$lookup（推荐） #

在$lookup阶段直接筛选指定用户的video_impression事件，仅关联目标用户的浏览记录，最后判断关联结果为空即可：

db.videos.aggregate([
  // 第一步：筛选符合基础条件的视频
  {
    $match: {
      "status": "complete",
      "privacy": "public"
    }
  },
  // 第二步：关联analytics，仅匹配指定用户的视频浏览记录
  {
    $lookup: {
      from: "analytics",
      let: { videoId: "$_id" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$data.video", "$$videoId"] },
                { $eq: ["$user", "1"] }, // 替换为你需要指定的用户ID
                { $eq: ["$event", "video_impression"] }
              ]
            }
          }
        }
      ],
      as: "user_impressions"
    }
  },
  // 第三步：筛选该用户无浏览记录的视频
  {
    $match: {
      "user_impressions": { $eq: [] }
    }
  },
  // 可选：移除关联字段，仅保留视频本身信息
  {
    $project: {
      user_impressions: 0
    }
  }
])

方法2：先聚合用户浏览的视频ID再匹配 #

先从analytics中提取指定用户浏览过的所有视频ID集合，再筛选不在该集合中的视频，适合用户浏览记录较多的场景：

db.videos.aggregate([
  {
    $match: {
      "status": "complete",
      "privacy": "public"
    }
  },
  {
    $lookup: {
      from: "analytics",
      pipeline: [
        {
          $match: {
            "user": "1", // 指定用户ID
            "event": "video_impression"
          }
        },
        {
          $group: {
            _id: null,
            viewedVideos: { $addToSet: "$data.video" }
          }
        }
      ],
      as: "user_viewed"
    }
  },
  {
    $unwind: {
      path: "$user_viewed",
      preserveNullAndEmptyArrays: true // 兼容用户无任何浏览记录的情况
    }
  },
  {
    $match: {
      $expr: {
        $not: { $in: ["$_id", "$user_viewed.viewedVideos"] }
      }
    }
  },
  {
    $project: {
      user_viewed: 0
    }
  }
])

关键说明 #

两种方法核心都是针对指定用户过滤浏览记录，而非全局匹配所有用户的记录。方法1更直观，适合大多数场景；方法2通过预聚合减少关联数据量，在大数据量下性能更优。

内容的提问来源于stack exchange，提问作者Cryptobyte