MongoDb:聚合$lookup

Question

给定以下集合。我必须获取title字段并合并到标识符

凭据：

{
    _id: ..
    title: ..
}

USER_CREDENTIAL：

{
    _id: ..
    credential_id: .. (from credential collection)
    created_at: ..
    identifier: {
        first_name: ..
        middle_name: ..
        last_name: ..
    }
}

响应应该是：

{
   user_credential_id: 
   member: {
        first_name:
        middle_name:
        last_name:
        title:
        created_at:
    }
}

Answer 1

1. $lookup -使用管道连接user和user_credential_id以匹配条件($match)，并修饰文档
   1. $lookup-反构造多个($project).
   2. $unwind -修饰输出文档的$match数组。

db.user.aggregate([
  {
    "$lookup": {
      "from": "user_credential",
      let: {
        "user_credential_id": "$_id",
        "title": "$title"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$credential_id",
                "$$user_credential_id"
              ]
            }
          }
        },
        {
          $project: {
            first_name: "$identifier.first_name",
            middle_name: "$identifier.middle_name",
            last_name: "$identifier.last_name",
            title: "$$title",
            created_at: "$created_at",
            
          }
        }
      ],
      "as": "member"
    }
  },
  {
    $unwind: "$member"
  },
  {
    $project: {
      _id: 0,
      user_credential_id: "$_id",
      member: 1
    }
  }
])

Sample Mongo Playground

Answer 2

您可以尝试此查询

db.user.aggregate([
  {
    $lookup: {
      from: "credentials",
      localField: "credential_id",
      foreignField: "_id",
      as: "user_credential"
    }
  },
  {
    $unwind: "$user_credential"
  },
  {
    $project: {
      _id: 0,
      user_credential_id: "$credential_id",
      member: {
        first_name: "$identifier.first_name",
        middle_name: "$identifier.middle_name",
        last_name: "$identifier.last_name",
        title: "$user_credential.title",
        created_at: "$created_at",
        
      }
    }
  }
])

你可以在here上查看