MongoDB嵌套对象Lookup问题：如何获取hobbies中的subHobbies数据？

我需要从hobbies集合的subHobbies数组中，匹配survey集合里surveyTargetHobbies数组中的ID，获取对应的subHobbies标题，但当前用$lookup的聚合代码无法实现。

现有聚合代码：

{
  $lookup: {
    from: "hobbies",
    let: {
      vid: "$surveyTargetHobbies",
    },
    pipeline: [
      {
        $match: {
          $expr: {
            $in: [
              "$_id",
              {
                $map: {
                  input: "$$vid",
                  in: {
                    $toObjectId: "$$this",
                  },
                },
              },
            ],
          },
        },
      },
    ],
    as: "surveyTargetHobbies",
  },
}

集合数据示例 #

survey集合数据

{
  "_id": {
    "$oid": "66012b133fa457ed3a999ca7"
  },
  "surveyName": "new survey",
  "surveyStartDate": {
    "$date": "2024-03-26T21:00:00.000Z"
  },
  "surveyEndDate": {
    "$date": "2024-03-29T21:00:00.000Z"
  },
  "surveyTargetGender": "all",
  "surveyTargetHobbies": [
    "6547a0f8aa29e1d75ba8d8a2",
    "6547a0f9aa29e1d75ba8d8a4"
  ],
  "createdAt": {
    "$date": "2024-03-25T07:43:15.298Z"
  },
  "updatedAt": {
    "$date": "2024-03-25T07:43:18.314Z"
  },
}

hobbies集合数据

{
  "_id": {
    "$oid": "6547a0e0e1ed3d290303c04f"
  },
  "title": "Dating",
  "subHobbies": [
    {
      "title": "Long-term Relationship",
      "_id": {
        "$oid": "6547a0f8aa29e1d75ba8d8a2"
      }
    },
    {
      "title": "Short-term Relationship",
      "_id": {
        "$oid": "6547a0f9aa29e1d75ba8d8a4"
      }
    }
  ]
}

问题分析 #

现有代码的$match条件是判断hobbies的_id是否在转换后的surveyTargetHobbies数组里，但实际上surveyTargetHobbies里的ID是subHobbies的_id，不是hobbies的_id，匹配逻辑完全错误，自然拿不到目标数据。

正确的聚合管道实现 #

[
  {
    $lookup: {
      from: "hobbies",
      let: {
        hobbyIds: {
          $map: {
            input: "$surveyTargetHobbies",
            in: { $toObjectId: "$$this" }
          }
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $gt: [
                {
                  $size: {
                    $filter: {
                      input: "$subHobbies",
                      cond: { $in: ["$$this._id", "$$hobbyIds"] }
                    }
                  }
                },
                0
              ]
            }
          }
        },
        {
          $project: {
            subHobbies: {
              $filter: {
                input: "$subHobbies",
                cond: { $in: ["$$this._id", "$$hobbyIds"] }
              },
              _id: 0
            }
          }
        },
        {
          $unwind: "$subHobbies"
        },
        {
          $project: {
            _id: "$subHobbies._id",
            title: "$subHobbies.title"
          }
        }
      ],
      as: "surveyTargetHobbies"
    }
  }
]

代码解释 #

1. $lookup的let参数：先把surveyTargetHobbies中的字符串ID转换成ObjectId，保证类型匹配。
2. pipeline内的$match：筛选出包含至少一个目标subHobbies的hobbies文档，通过$filter和$size判断是否存在匹配项。
3. 第一次$project：过滤出hobbies文档中完全匹配的subHobbies数据，剔除无关内容。
4. $unwind：将嵌套的subHobbies数组展开为单个文档，简化后续结构。
5. 第二次$project：提取subHobbies的_id和title字段，得到整洁的目标数据。

执行后，surveyTargetHobbies字段会输出匹配到的subHobbies对象数组：

"surveyTargetHobbies": [
  {
    "_id": ObjectId("6547a0f8aa29e1d75ba8d8a2"),
    "title": "Long-term Relationship"
  },
  {
    "_id": ObjectId("6547a0f9aa29e1d75ba8d8a4"),
    "title": "Short-term Relationship"
  }
]

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