MongoDB聚合：从嵌套对象数组中筛选指定对象

需求：从给定的MongoDB文档中，筛选出unified_invitees数组里满足**milestone_status最后一个元素的milestone值为subscribed**的对象，最终输出仅保留符合条件的unified_invitees项，无需保留顶层的active、time等字段。

样本数据 #

{
    active: 'true',
    time:'',
    unified_invitees: [
        {
            _id: 1,
            milestone_status: [
                { milestone:"draft" },
                { milestone:"deleted" }
            ]
        },
        {
            _id: 2,
            milestone_status: [
                { milestone:"draft" },
                { milestone:"deleted" }
            ]
        },
        {
            _id: 3,
            milestone_status: [
                { milestone:"draft" },
                { milestone:"subscribed" }
            ]
        }
    ]
}

你尝试过的方案 #

1. 两种$addFields写法 #

第一种：

{
    "invitee":{
          $filter: { input: "$unified_invitees.milestone_status", cond: { $eq: [ "$$this.milestone", "subscribed" ] } }
        }
}

第二种：

{
  "unified_invitees": {
      "$arrayElemAt": [
          {
              "$filter": {
                  "input": "$unified_invitees",
                  "as": "comp",
                  "cond": {
                      "$ne": [ "$$comp.milestone_status.milestone", "deleted" ]
                  }
              }
          }, 0
      ]
  }
}

2. $filter+$map写法 #

{
    "documents": {
      $map: {
        "input": "$unified_invitees",
        "as": "d",
        "in": {
          "milestone_status": {
            $filter: {
              "input": "$$d.milestone_status",
              "as": "s",
              "cond": {
                $eq: [
                  "$$s.milestone",
                  "subscribed"
                ]
              }
            }
          }
        }
      }
    }
}

正确的聚合管道方案 #

你的核心需求是判断milestone_status数组的最后一个元素的milestone值，之前的方案要么没定位到最后一个元素，要么筛选逻辑偏离需求。以下是精准实现的两种方案：

方案一：一步到位的简洁写法 #

直接在$project中嵌套$last和$filter，无需多管道步骤，高效简洁：

db.collection.aggregate([
  {
    $project: {
      unified_invitees: {
        $filter: {
          input: "$unified_invitees",
          as: "invitee",
          cond: { $eq: [ { $last: "$$invitee.milestone_status.milestone" }, "subscribed" ] }
        }
      },
      _id: 0
    }
  }
])

方案二：分步拆解写法（适合理解逻辑） #

如果需要更清晰的步骤拆分，可以用以下管道：

1. $project：给每个unified_invitees项添加last_milestone字段，记录其milestone_status的最后一个值
2. $unwind：展开数组方便筛选
3. $match：过滤出符合条件的项
4. $group：重新组合成数组
5. $project：清理输出字段

db.collection.aggregate([
  {
    $project: {
      unified_invitees: {
        $map: {
          input: "$unified_invitees",
          as: "invitee",
          in: {
            _id: "$$invitee._id",
            milestone_status: "$$invitee.milestone_status",
            last_milestone: { $last: "$$invitee.milestone_status.milestone" }
          }
        }
      }
    }
  },
  { $unwind: "$unified_invitees" },
  { $match: { "unified_invitees.last_milestone": "subscribed" } },
  {
    $group: {
      _id: null,
      unified_invitees: { $push: { _id: "$unified_invitees._id", milestone_status: "$unified_invitees.milestone_status" } }
    }
  },
  { $project: { _id: 0, unified_invitees: 1 } }
])

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