MongoDB嵌套对象数组多条件查询问题求助（含更新数据集）

需求说明 #

• 目标集合中（初始为Users，更新后数据结构嵌套了user_infos数组），需筛选满足以下条件的用户：
  1. 用户的events数组至少包含一条category为"Analytics"的事件
  2. 该用户events数组中最后一条category为"Analytics"的事件，其event字段值必须为"Views"

初始数据集（直接包含events字段） #

[
    {
      _id: "123",
      events: [
        { category: "Payment", event: "ok" },
        { category: "Analytics", event: "Views" },
        { category: "Analytics", event: "Clicks" }
      ]
    },
    {
      _id: "456",
      events: [
        { category: "Payment", event: "ok" },
        { category: "Analytics", event: "Clicks" },
        { category: "Payment", event: "failed" }
      ]
    },
    {
      _id: "127",
      events: [
        { category: "Payment", event: "ok" },
        { category: "Analytics", event: "Clicks" },
        { category: "Analytics", event: "Views" }
      ]
    }
  ]

预期查询结果 #

[{
    "_id": "127",
    "events": [
      { "category": "Payment", "event": "ok" },
      { "category": "Analytics", "event": "Clicks" },
      { "category": "Analytics", "event": "Views" }
    ]
  }]

原查询的问题分析 #

你提供的聚合查询存在以下问题：

1. 字段路径错误："users.events.category"应为直接的"events.category"（集合是users，文档内没有嵌套users字段）
2. 拼写错误："Analyics"应为"Analytics"
3. 未满足核心条件：仅匹配存在Analytics和Views事件，但无法保证最后一条Analytics事件是Views

针对初始数据集的正确聚合查询 #

db.users.aggregate([
  // 第一步：先过滤出包含Analytics事件的用户，减少后续计算量
  {
    $match: {
      "events.category": "Analytics"
    }
  },
  // 第二步：提取所有category为Analytics的事件到临时数组
  {
    $addFields: {
      analytics_events: {
        $filter: {
          input: "$events",
          cond: { $eq: ["$$this.category", "Analytics"] }
        }
      }
    }
  },
  // 第三步：过滤临时数组非空，且最后一个元素的event为Views
  {
    $match: {
      analytics_events: { $ne: [] },
      "analytics_events.-1.event": "Views"
    }
  },
  // 可选：移除临时字段，保留原文档结构
  {
    $project: {
      analytics_events: 0
    }
  }
])

更新后的数据集（嵌套user_infos数组） #

[{
    user_infos: [{
      _id: "123",
      events: [
        { category: "Payment", event: "ok" },
        { category: "Analytics", event: "Views" },
        { category: "Analytics", event: "Clicks" }
      ]
    }]
  },
  {
    user_infos: [{
      _id: "456",
      events: [
        { category: "Payment", event: "ok" },
        { category: "Analytics", event: "Clicks" },
        { category: "Payment", event: "failed" }
      ]
    }]
  },
  {
    user_infos: [{
      _id: "127",
      events: [
        { category: "Payment", event: "ok" },
        { category: "Analytics", event: "Clicks" },
        { category: "Analytics", event: "Views" }
      ]
    }]
  }
]

针对更新后数据集的正确聚合查询 #

如果需要保留原文档的user_infos数组结构，可使用以下查询：

db.users.aggregate([
  // 展开user_infos数组，便于处理每个用户的events
  { $unwind: "$user_infos" },
  // 过滤包含Analytics事件的用户信息
  {
    $match: {
      "user_infos.events.category": "Analytics"
    }
  },
  // 提取该用户的所有Analytics事件到临时字段
  {
    $addFields: {
      "user_infos.analytics_events": {
        $filter: {
          input: "$user_infos.events",
          cond: { $eq: ["$$this.category", "Analytics"] }
        }
      }
    }
  },
  // 过滤最后一条Analytics事件为Views的用户
  {
    $match: {
      "user_infos.analytics_events": { $ne: [] },
      "user_infos.analytics_events.-1.event": "Views"
    }
  },
  // 移除临时字段
  {
    $project: {
      "user_infos.analytics_events": 0
    }
  },
  // 将user_infos重新组合为数组，恢复原结构
  {
    $group: {
      _id: "$_id",
      user_infos: { $push: "$user_infos" }
    }
  }
])

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